Ranking cells largest to smallest
 

I've got a large amount of data that I've sorted with a pivot table. I need
to order the data within each column of the pivot table (outside of the pivot
table).

Example:
Frequency
A 6
B 7
C 6
D 5
E 4

Obviously, the largest number in the frequency column is 7 and obviously,
the next largest number is 6. What I'm trying to do is to get Excel to list
the results by row heading (i.e., B is the largest, A is the next largest, C
is the next largest, etc.) even when there is a duplicate number. I've been
using the =large() function to no avail (it gives me a value of 6 for both
the second and third largest values). How do I get Excel to differentiate
between the two distinct values of "6"?

Thanks in advance,

Jim314

Assuming your data is in A1:B5, here's an array formula, which, when entered
in C1 and copied down through C5, gives B A C D E:

=INDEX(A$1:A$5,MATCH(LARGE($B$1:$B$5+1-ROW($B$1:$B$5)/100,ROWS($C$1:$C1)),$B$1:$B$5+1-ROW($B$1:$B$5)/100,0))

Be sure to enter it with CTRL+SHIFT+ENTER.

On Mon, 10 Jan 2005 11:05:01 -0800, jim314
wrote:

I've got a large amount of data that I've sorted with a pivot table. I need
to order the data within each column of the pivot table (outside of the pivot
table).

Example:
Frequency
A 6
B 7
C 6
D 5
E 4

Obviously, the largest number in the frequency column is 7 and obviously,
the next largest number is 6. What I'm trying to do is to get Excel to list
the results by row heading (i.e., B is the largest, A is the next largest, C
is the next largest, etc.) even when there is a duplicate number. I've been
using the =large() function to no avail (it gives me a value of 6 for both
the second and third largest values). How do I get Excel to differentiate
between the two distinct values of "6"?

Thanks in advance,

Jim314