Luke,

Your use of LARGE has the same limitations as the RANK approach when dealing with ties.

HTH,
Bernie
MS Excel MVP


"Luke M" wrote in message
...
In column C
=LARGE($B$1:$B$5,ROW())

Copy down as far as you want. FYI, if you want to sort the other way, use
SMALL function.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"LiAD" wrote:

Afternoon,

I have data such which i would like to rank and then order the ranked data
in highest to lowest, all automatically so it re-orders when data changes.

My technique so far was maybe slightly complicated

Example of data in col A and B

a 1
b 2
c 3
d 4
e 1

I then use =RANK(B1;$B$1:$B$5) and will it ouput in col C (written left to
right instead of top to bottom), 4,3,2,1,4

In col D I put the order of the data I want, highest at the top so it reads
top to bottom 1,2,3,4,5

In col E I use =INDEX($B$1:$B$5;MATCH(D1;$C$1:$C$5;0)) so that excel finds
the number from col B that is associated with the highest ranked position.
Excel will return 4,3,2,1,n/a

How can I avoid the n/a problem when I have more than one set of data of
equal ranking but keep it automatic?

Any ideas?