For this exercise count is good. This will then enable me to use a vlookup
to identify ID.

"Aladin Akyurek" wrote:

OK, I'll apply the formula system to the sample you provided. Do you
want the ranking to be based on "Counts" or "Bill" amounts?

gb_S49 wrote:
Example
ID Counts Bill Ranking
AB 845 £12 1
AC 424 £865 2
AD 249 £632 3
AE 231 £77 4
AF 121 £4 5
AG 87 £552 6
AH 85 £76 7
AI 81 £4 8
AJ 72 £3 9
AK 63 £17 10
AL 60 £16 11
AM 53 £759 12
AN 51 £110 13
AP 50 £333 14
AO 41 £938 15
AQ 35 £940 18
AR 35 £904 18
AS 35 £135 18
AT 33 £376 19
AU 29 £840 21
AX 29 £469 21


"gb_S49" wrote:


Thank you. you did post previously but I lost my internet connection.
I must be doing something wrong using
=RANK(B3,$B$3:$B$9)+COUNTIF(B3:$B$3,B3)-1
I am still getting repeat values

"Aladin Akyurek" wrote:


Did you investigate my contrib under the link

http://tinyurl.com/44ywo

I quoted in a reply to a previous post of yours?

gb_S49 wrote:

I am trying to set up a template work sheet where new data is dumped into
columns A to C. I need to rank in order (top 20) the values of column c, but
report back the adjacent value of C. Column B may contain the same value.
Column C contain unique value.
Cell h2 =LARGE($B:$B,$J2){j2 = 1} gives me the highest value of column b.
Cell F2 =VLOOKUP($H2,$A:$C,3,0)
F2 only returns the first occurance.
Any suggestions?