Software

Breaking Excel's three-column sort barrier

Who says Excel can't sort on more than three columns at a time? Here's a tip that lets you sort data on as many columns as you need.

Excel’s built-in sorting options let you sort rows of data based on the values in as many as three columns. You just go to Data | Sort and then select the appropriate columns for the Sort By option (the primary sort key) and up to two columns from the Then By fields.

Most of the time, you don’t need more than three sort options. Every now and then, however, you’ll need to sort your data by the values in four or more columns. When that happens, there’s no need to panic. Just insert a new column and use a formula to concatenate (or combine) the values from four or more columns into a single column. Then use that column as your sort key.

Fortunately, Excel 97 and Excel 2000 make it easy to concatenate many values into a single string. And here’s the best part: You can mix and match entries with numeric and date values without converting these values into strings.

Concatenating the old-fashioned way
To demonstrate how this technique works, let’s create a custom sort key based on the four columns shown in Figure A: Account, Department, Date, and Item. The values in the Account and Department columns are strings (text entries), the values in the Date column are date values, and the entries in the Item column are numeric.

Figure A
We’ll create a custom sort key based on the values in all four columns.


Now, let’s create a single custom sort key based on the entries in the other four columns. To do so, insert a new column A and then enter the following formula in cell A2:
+B2&C2&D2&E2

Note that you must use the plus sign [+] to begin the formula, but you need to use the ampersand [&] (the concatenation operator) for the remaining cell references. The result will be a long string that consists of the entries in cells B2, C2, D2, and E2. When you’re ready to sort your data, specify column A, your custom sort field, in the Sort By field.

As Figure B shows, Excel will display the dates as Julian values. (We circled the date and its corresponding Julian value in the first row of data.) Since this is a “dummy” field for sorting, the Julian date doesn’t hurt a thing. That component of your custom sort field will still sort your data in chronological order.

Figure B
Here’s what our data looks like after we sorted our data on column A, which contains our custom sort key. (Note that Excel converted the date entry to a Julian value.)


Using the CONCATENATE function
You can also create your custom sort key using the CONCATENATE worksheet function. Here's its syntax:
=CONCATENATE(first_thing,second_thing,third_thing,...)

You can replace first_thing, second_thing, and third_thing with function calls, cell references, range names, or literal strings. (If you want to include a literal string, you must enclose the character or characters in quotation marks.) The CONCATENATE function combines all the items you list into a single string. Figure C shows some sample strings generated by the CONCATENATE function.

Figure C
The CONCATENATE function makes it easy to create a custom sort key.

Get valuable tips for using worksheet functions, VBA code, and much more, all delivered straight to your inbox every Wednesday and Friday. Best of all, it's absolutely free. Sign up for Microsoft Office Suite—one TechMail that will bring you power tips for working with Excel, Word, and Access.
0 comments

Editor's Picks