Forum Discussion

ksp_87's avatar
ksp_87
Copper Contributor
Mar 13, 2018

Excel sort - Smaller to Largest

Hi,

 

I have the below table and i am trying to sort the data from small to larger for all the columns at the same time. If i try to sort, only column A is getting sorted and there is no change with respect to other columns. If i use custom sort the same thing happens where column A is sorted and the data corresponding to column A in other columns B,C and D gets sorted and not the entire list. Is there a was to sort out the 4 columns from small to large at the same time?

 

 

A B C D
61.54 49.24 102.48 101.31
34.25 62.17 105.25 94.27
72.22 37.93 82.53 97.34
42.93 59.47 77.64 224.36
24.01 61.26 75.65 97.34
37.76 67.16 162.81 71.50
55.38 51.56 75.65 73.66
33.52 51.56 67.26 98.86
63.78 76.46 69.28 74.02
39.90 64.77 60.68 98.98
25.41 75.07 91.36 102.81
38.22 61.54 61.66 65.94
25.41 50.10 61.66 64.52
  36.13 156.92 49.06
  90.03 73.16 126.14
  48.46 78.82 60.34
  29.12 81.53 46.03
  45.05 78.32 44.00
  66.38 65.94 68.09
  55.38 56.10 92.24
  49.02 43.41 59.47
  35.38 107.83 63.13
  79.50 53.67 47.14
  45.04 37.97 111.38
  30.18 40.50 56.09
  44.97 55.14 42.78
  30.18 69.44 43.98
    59.47 66.31
    54.89 43.98
    42.00  
    95.22  
    49.88  
    35.30  
    40.76  
    54.31  
    40.76  
excel

3 Replies

  • ksp_87's avatar
    ksp_87
    Copper Contributor
    Mar 13, 2018
    Thanks for responding. I have 2000 columns and it will take loads of time to do that individually. Looking if it can be done through macro
    • Haytham Amairah's avatar
      Haytham Amairah
      Silver Contributor
      Mar 14, 2018

      Hi,

       

      Sorry for the delay in reply!

      I've written this macro for you:

      Sub SortingMacro()
'Sort each column individually in ascending order
'Written by Haytham Amairah
'Last Updated: 3/14/2018
    Application.ScreenUpdating = False
    On Error Resume Next
    
    For Each col In ActiveWorkbook.ActiveSheet.UsedRange.Columns
        col.EntireColumn.Select
        r = col.EntireColumn.Address
        
        ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Add Key:=Range(r), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        
        With ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort
        .SetRange Range(r)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        End With
    Next
    
    Range("A1").Select
    On Error GoTo 0
    Application.ScreenUpdating = True

End Sub

       

      Please test it and refer back to me if you have any feedback.

       

      Hope that helps

      Haytham

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Mar 13, 2018

    Hi,

     

    The only solution I know is to entirely select each column and then sort it individually.

    You will see a message asking you to expand the selection or to continue with the current selection!

    Select continue with the current selection and then hit Sort.

     

    Do the same thing for each column.

     

    Hope that helps

    Haytham