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?

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?

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?

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?

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?

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?