Excel Hacks Ebook [Electronic resources] نسخه متنی

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.