In Sort On More Than Three Columns, I described how to short your data in two steps. Mark’s tip for sorting on more than three columns uses a helper column.
Using another column, you can concantenate the columns you want to sort and sort on that column. This example sorts on four columns by using a fifth column to string together the other four.
There is a problem with this method. In the screen shot below, Dick’s week 5 entry is sorted above John’s. The ‘Number’ column is coverted to text in the concatenation process and 18 is less than 5 when their both considered to be text. Text is sorted by comparing characters from left to right.
So be careful if you use this method. Thanks for the tip, Mark.
Use the TEXT formula to format the numeric field and for the Aplabetic Fields use the REPT function to append spaces to the end.
=TEXT(B2,”00?)&TEXT(C2,”000?)&TEXT(D2,”00?)&A2&REPT(” “,20-LEN(A2))
NameWeekAmountNumberSort
John2600160260016John
Tom3300190330019Tom
Dick4100140410014Dick
John4200120420012John
Dick5300180530018Dick
Tom5800140580014Tom
John650050650005John
Tom710090710009Tom
Dick8400190840019Dick
Dick, did you ever get anywhere with the fuzzy sort thing you posted a while back – i had a quite look one day (when i needed to do something similar) but could’t reallt offer anything new.
Tom’s approach is also useful to set up a lazy-man’s multi column lookup.
– Jon
This is also useful for multi conditional SUMIF and COUNTIF.
I used many times.
Zoltan
I agree with all the above.
I use multi-column concatenations all the time for sorting, lookup and area formula purposes. It works like a charm but as discussed above it has to be set up properly. For example, if one column is expected to have numbers of one to five digits, then those numbers have to left-padded with zeros with Text(b2,”00000?) …