User Defined Multiple Column Sort in Excel

Question

Hello,

I have few hundred rows of data in four columns (varA, varB, varC, varD). I want to write a macro such that a user can define the sort order of these columns i.e. first sort by varD, then by varA, varC, varB...so on and so forth. In all there are 4! or 24 different permutation possibilities. I DO NOT WANT THE USERS TO GO DIRECTLY RUN SORT ON THE DATA, but rather have a way for them to define the order they want the columns sorted. One way I was thinking was giving them a table with these column names and they can just provide rank (1 to 4). Based on these ranks, the macro will dynamically determine the sort order.

Any help is greatly appreciated.

Thanks, chintoo

Answer 1

I'm not familiar with newer versions of excel, so I don't know if they have something to make this easier.

Here is what I would do

1. Arrange the columns by rank
2. Sort by columns A, B, C, D
3. Arrange the columns back to the original order

Here is how I would lay out the worksheet

Header row is in row 2. Data starts in row 3 and down.

   A      B      C      D                
1  
2  varA   varB   varC   varD
3  data1  data2  data3  data4
4  data5  data6  data7  data8

User enters the column ranks in row 1

   A      B      C      D                
1  2      4      3      1
2  varA   varB   varC   varD
3  data1  data2  data3  data4
4  data5  data6  data7  data8

Add column number in front of column header names

   A      B      C      D                
1  2      4      3      1
2  1varA  2varB  3varC  4varD
3  data1  data2  data3  data4
4  data5  data6  data7  data8

Sort by ROW 1 (sort left to right, arranging columns by rank)

   A      B      C      D
1  4      3      2      1
2  2varB  3varC  1varA  4varD
3  data2  data3  data1  data4
4  data6  data7  data5  data8

Sort by COLUMN A, B, C, D or D, C, B, A (depending on what you want)

   A      B      C      D
1  4      3      2      1
2  2varB  3varC  1varA  4varD
3  data2  data3  data1  data4
4  data6  data7  data5  data8

Sort by ROW 2 (arrange columns back in the original order)

   A      B      C      D                
1  2      4      3      1
2  1varA  2varB  3varC  4varD
3  data1  data2  data3  data4
4  data5  data6  data7  data8

Remove column number in front of column header names

   A      B      C      D                
1  2      4      3      1
2  varA   varB   varC   varD
3  data1  data2  data3  data4
4  data5  data6  data7  data8

Answer 2

You can use this function to determine the correct sort order:

Function getColumnByRank(rankSearch As Byte) As Range

    Dim c As Byte

    For c = 1 To 4
        With ActiveWorkbook.Worksheets("Sheet1")
            If .Cells(1, c).Value = rankSearch Then Set getColumnByRank = Cells(3, c)
        End With
    Next

End Function

The way I have the records set up, is that the rank values are on row 1, there is a blank row between the ranks and the headers (excel screws up the header references if I don't do that) and then have the tabular data below.

   A      B      C      D                
1  1      4      2      3
2
3  varA   varB   varC   varD
4  data1  data2  data3  data4
5  data5  data6  data7  data8

So now you can use the getColumnByRank function in the .Sort method used by Alex to determine the column reference.

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=getColumnByRank(1), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=getColumnByRank(2), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=getColumnByRank(3), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=getColumnByRank(4), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

If you want to move your rankings to a different sheet or whatever, just change the function to accommodate that. HTH