Hack 29 Sort by More Than Three Columns

   

Hack 29 Sort by More Than Three Columns

Excel's Sort feature is limited in that it enables you to nominate no more than three data fields by which to sort. In most cases, this is enough, but sometimes it can be handy to sort by more than three columns of data. Here is how you can get around this limitation .

For this example, we will assume you have related data in columns A, B, C, D, and E, and you want to sort this data first by column A, then B, then C, then D, and then E. To do this, you need to be able to sort backward in other words, sort by the last field first, and then work back to the first field.

Select columns A through E and then select Data Sort. Select the sort order by specifying that column C be sorted first, then D, and then E. Click Sort. Now select columns A through E and select Data Sort. This time, sort by column A and then by B. Click Sort, and everything will be in order. Excel will have sorted the columns by five fields instead of the usual three.

If you want to automate this task, you can use a macro that will sort the selection and guess whether your data has column headings based on the formatting of the first row in the selection. If headings are in bolded, Excel will know they are column headings and will not sort them. Instead, it will sort by the leftmost column first, through to the rightmost column, for any number of columns up to 256.

The macro code you need to use must be placed into a standard module. To get it there, select Tools Macro Visual Basic Editor (Alt/Option-F11), then select Insert Module and enter this code:

 Sub SortByX( ) Dim l As Long   For l = Selection.Columns.Count To 1 Step -1    Selection.Sort Key1:=Selection.Cells(2, l), _     Order1:=xlAscending, Header:=xlGuess, Orientation:=xlTopToBottom Next l End Sub 

To return to Excel, either close the window or press Alt/ -Q. Once you have the hang of it, you will be able to perform much more complicated sorts than just the standard types on offer.