Change Row to Column Based on Rank Function

**oyz79** · 09-16-2011, 10:07 PM

I have a row of stock symbols at the top of my Excel 2007 workbook, call it row A. Several rows below this I have a numeric value for each stock symbol, based on a rank function, call it row Z.

I want to take the symbols in the top row of my workbook and display them as a column on a new sheet, in order of the numeric rank assigned to each symbol.

For example, stock ABC has rank value of 3, XYZ is ranked 1, and AAA is ranked 2. I want the new sheet to display:

XYZ
AAA
ABC

Any idea on how to code this?

**mikerickson** · 09-16-2011, 10:38 PM

Let's call them Row 1 and Row 10, since rows have numbers and columns have letters.

If the numbers are unique, this formula, dragged down, should work,

=INDEX(Sheet1!$1:$1, 1, MATCH(SMALL(Sheet1!$10:$10,ROW(A1)), Sheet1!$10:$10, 0))

**oyz79** · 09-16-2011, 10:46 PM

Thanks, one question/additional detail - the data in Row 1 starts in column D, as does the data in row 10 (column D), and both end at column CZ. I'm assuming there needs to be some modification to the formula?

**mikerickson** · 09-17-2011, 01:43 AM

Does it work as written?
What alterations have been tried?

**oyz79** · 09-17-2011, 11:45 AM

It did not work as is. Here is what I've tried so far with no success

=INDEX(Performance Dashboard!$D3:$D3, 1, MATCH(SMALL(Performance Dashboard!$D946:$D946,ROW(D3)), Performance Dashboard!$D946:$D946, 0))

=INDEX(Performance Dashboard!$D3:$CZ3, 1, MATCH(SMALL(Performance Dashboard!$D946:$CZ946,ROW(D3)), Performance Dashboard!$D946:$CZ946, 0))