Many Excel reports include tables that show sorted results. Usually, these tables were sorted manually in Excel, using the Data, Sort command. However, reports would be a lot easier to maintain and update if formulas (not macros) could sort the data automatically.
There’s a simple way to do this. But to make the method work reliably, you’ve got to fix the behavior of the RANK function.
This figure shows a partial solution, and illustrates the problem with RANK. Columns A, B, and G show actual data. Enter the following formulas for the cells shown, and copy the formulas down their columns as needed:
C3: =RANK(A3,$A$3:$A$6,0)
E3: =MATCH(G3,$C$3:$C$6,0)
H3: =INDEX($B$3:$B$6,E3)
I3: =INDEX($A$3:$A$6,E3)
The formulas in column C rank the sales values, where the largest value is ranked 1 and the smallest in the table is ranked 4.
The easiest way to understand column E is with an example. Look at cell G6. This marks the fourth sorted row in the table at the right. Cell E6 tells us that the values for this row can be found in the second row of the table at its left. The other values in column E provide similar information.
Columns H and I use the information in column E to return the appropriate values.
The reason row 4 of the spreadsheet has #N/A values is because the RANK function assigns identical rankings to identical values. This is a problem because Coats and Pants have identical sales this month. So the RANK function assigns the same #1 ranking to both products in column C, and assigns no #2 rank value at all. Therefore, when the formula in cell E4 looks for a #2 ranking, it returns #N/A.
This problem is easy to fix, however. Just insert a new column for an adjusted sales value and then rank those adjusted values. Here are the formulas for the cells shown:
B3: =A3+0.000001*ROW()
D3: =RANK(B3,$B$3:$B$6,0)
Column B adds a tiny amount to each Sales value, an amount based on the number of each row in the table. This forces each value in Sales2 to be unique, which forces the Rank values in column D to be unique. And this gives us our automatically sorted values in columns H:J.
When you use this technique, be sure to estimate the maximum number of rows in your table when you choose the decimal fraction shown in the formula for cell B3. That is, if you’re going to have hundreds of thousands of rows of data (which New Excel allows) make sure that you still have a small number when you multiply your decimal fraction by a row number of about one million.
