The impossible?

[Guest]

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?

 

[Aladin Akyurek]

Did you investigate my contrib under the link

http://tinyurl.com/44ywo

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

 

[Guest]

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

 

[Guest]

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

 

[Aladin Akyurek]

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?

 

[Guest]

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

 

[Aladin Akyurek]

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,D424,0))=B4:B24,D424))-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.

 

[Guest]

This is a very excellent presentation.

Thank-you.

 

[Guest]

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.