More Sorting Multiple Columns

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.

Posted in Uncategorized

5 thoughts on “More Sorting Multiple Columns

  1. 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))


  2. 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.

  3. 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?) …

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.