For the longest time I would always sort my data by using the the sort feature under the Excel Data menu. The thing that always bothered me when doing this is that it’s not automatic. If I am working with data that is constantly being updated then I will have to constantly be running this sort. Here’s a way that you can create sorted data that is based on unsorted data.

You can start by getting some numbers (using the RANDBETWEEN function) into column B. These will be the numbers that we want to sort into column C. We can do this by using the LARGE(array,k) function which will return the n^th (or in this case the “k^th“) largest number in the array that you select.

For the array we’ll reference the numbers in column B but we still need something to put into the k parameter to designate which place we are at in the sort. To accomplish this I use the ROW function. Doing this avoids having to create another column with the numbers needed for k. To get the number we need we can take the row of cell C3 and subtract the row of cell C2. For C3 this would give us 1, which, when used in the large function, will give us the 1st largest number in the range.

The formula in cell C3 will come to look like this:

=LARGE($B$3:$B$12,ROW()-ROW($C$2))

You can then drag this down to fill the rest of the rows and you now have your data sorting automatically no matter what is in column B.

What if I have multiple columns that need to be automatically sorted?

Likely if you have a big block of data that you are sorting by one column you’ll want the other columns to move with the sorted column. To do this you can create one column that does the initial sort as described above and then you can use an INDEX MATCH combination below to take the value returned by the LARGE function and use that to find the rest of the information that should be on the same row.

=INDEX([array to return],MATCH([lookup value],[lookup array],[match type]))

Here’s an example workbook:Auto Sort

This entry was posted on Monday, May 21st, 2007 and is filed under Whatever, Excel. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.