=LARGE Function Question

[TN.Jim]

I need some help!!!
I have a list of physicians and corresponding surgical case per month that I
need to be able to manipulate...

Assume:
Tom 20
Will 22
Mike 30
Ed 22
John 22

The list is obviously much longer and there is data for each month.
I need to pull the top 5 for each month.
I tried to use
A7=LARGE(B1:B5,1) [30] B7=INDEX(A1:A5,(MATCH(A7,B1:B5,0)) [Mike]
A8=LARGE(B1:B5,2) [22] B8=INDEX(A1:A5,(MATCH(A8,B1:B5,0)) [Will]
A9=LARGE(B1:B5,3) [22] B9=INDEX(A1:A5,(MATCH(A9,B1:B5,0)) [Will]
etc..

But, when I have the same values (Will, Ed, and John = 22), I can only get
"Will."
Any ideas or suggestions?
THANKS!!!
Jim

 

[Frank Stone]

hi,
the formula that you are using only select the first
occurance which is why you only get will.
Suggestion. why not add a helper column and fill it when
descending number 1 to whatever.
then sort the data assending by the column that the top 5
would be in. your top 5 would be at the top. you could
then resort descending by the helper column to put
everything back the way is was.
yes, a bit machanical but if you only do it once a month,
it shouldn't be that much of a hassel.

regards
Frank

 

[Dave Peterson]

This may not help, either, but if you applied Data|Filter|autofilter, you could
filter on that quantity of cases column and do "Top 10" (it's adjustable to show
the top 5).

And ties will appear in that filtered list.

I need some help!!!
I have a list of physicians and corresponding surgical case per month that I
need to be able to manipulate...

Assume:
Tom 20
Will 22
Mike 30
Ed 22
John 22

The list is obviously much longer and there is data for each month.
I need to pull the top 5 for each month.
I tried to use
A7=LARGE(B1:B5,1) [30] B7=INDEX(A1:A5,(MATCH(A7,B1:B5,0)) [Mike]
A8=LARGE(B1:B5,2) [22] B8=INDEX(A1:A5,(MATCH(A8,B1:B5,0)) [Will]
A9=LARGE(B1:B5,3) [22] B9=INDEX(A1:A5,(MATCH(A9,B1:B5,0)) [Will]
etc..

But, when I have the same values (Will, Ed, and John = 22), I can only get
"Will."
Any ideas or suggestions?
THANKS!!!
Jim