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?

That you still mention VLOOKUP makes me believe that you probably missed
the point of the formula system I referred you to. Hope the application
to the sample you provided will clear up the issues.

Let A3:C24 house the sample, including the row of labels but excluding
the ranking column.

In order to better demonstrate the system I changed Counts for AM to 63.
Also, I've set the Top N value to 10. The Top N value can be any
convenient value you want.

In D4 enter & copy down:

=RANK(B4,$B$4:$B$24)+COUNTIF($B$4:B4,B4)-1

In E1 enter: 10

This is where you enter the N of Top N.

E2:

=MAX(IF(INDEX(B4:B24,MATCH(E1,D4:D24,0))=B4:B24,D4 :D24))-E1

which you need to confirm with control+shift+enter instead of the usual
enter.

In E4 enter and copy across to F4 then down:

=IF(ROW()-ROW(E$4)+1<=$E$1+$E$2,INDEX(A$4:A$24,MATCH(ROW()-ROW(E$4)+1,$D$4:$D$24,0)),"")

The area in E:F from E4 on will show you the Top N list of ID's and
their assciated Counts.

Just try as instructed and post back if you have any questions.

gb_S49 wrote:
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?

This is a very excellent presentation.

Thank-you.

"Aladin Akyurek" wrote:

That you still mention VLOOKUP makes me believe that you probably missed
the point of the formula system I referred you to. Hope the application
to the sample you provided will clear up the issues.

Let A3:C24 house the sample, including the row of labels but excluding
the ranking column.

In order to better demonstrate the system I changed Counts for AM to 63.
Also, I've set the Top N value to 10. The Top N value can be any
convenient value you want.

In D4 enter & copy down:

=RANK(B4,$B$4:$B$24)+COUNTIF($B$4:B4,B4)-1

In E1 enter: 10

This is where you enter the N of Top N.

E2:

=MAX(IF(INDEX(B4:B24,MATCH(E1,D4:D24,0))=B4:B24,D4 :D24))-E1

which you need to confirm with control+shift+enter instead of the usual
enter.

In E4 enter and copy across to F4 then down:

=IF(ROW()-ROW(E$4)+1<=$E$1+$E$2,INDEX(A$4:A$24,MATCH(ROW()-ROW(E$4)+1,$D$4:$D$24,0)),"")

The area in E:F from E4 on will show you the Top N list of ID's and
their assciated Counts.

Just try as instructed and post back if you have any questions.

gb_S49 wrote:
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?

Your absolutely right..I was trying to step through the original and expected
a different out come. Must learn to be more patient and follow through.
THANK YOU for YOUR Support.
RESULT is Better than I expected.

"Aladin Akyurek" wrote:

That you still mention VLOOKUP makes me believe that you probably missed
the point of the formula system I referred you to. Hope the application
to the sample you provided will clear up the issues.

Let A3:C24 house the sample, including the row of labels but excluding
the ranking column.

In order to better demonstrate the system I changed Counts for AM to 63.
Also, I've set the Top N value to 10. The Top N value can be any
convenient value you want.

In D4 enter & copy down:

=RANK(B4,$B$4:$B$24)+COUNTIF($B$4:B4,B4)-1

In E1 enter: 10

This is where you enter the N of Top N.

E2:

=MAX(IF(INDEX(B4:B24,MATCH(E1,D4:D24,0))=B4:B24,D4 :D24))-E1

which you need to confirm with control+shift+enter instead of the usual
enter.

In E4 enter and copy across to F4 then down:

=IF(ROW()-ROW(E$4)+1<=$E$1+$E$2,INDEX(A$4:A$24,MATCH(ROW()-ROW(E$4)+1,$D$4:$D$24,0)),"")

The area in E:F from E4 on will show you the Top N list of ID's and
their assciated Counts.

Just try as instructed and post back if you have any questions.

gb_S49 wrote:
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?