=LARGE question... Please Help Me!

G

Guest

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
 
H

hgrove

TN.Jim wrote...
...
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 onl get
"Will."

With the following in A1:B20,

A 3
B 1
C 2
D 1
E 2
F 4
G 3
H 3
I 1
J 3
K 3
L 3
M 3
N 1
O 4
P 2
Q 2
R 4
S 1
T 1

Enter the following formula in D1.

D1:
=LARGE(B$1:B$20,ROW())

Fill D1 down into D2:D5. Then enter the following formulas.

E1:
=INDEX(A$1:A$20,MATCH(D1,B$1:B$20,0))

E2: [array formula]
=INDEX(A$1:A$20,MATCH(D2,B$1:B$20*(COUNTIF(E$1:E1,A$1:A$20)=0),0))

Then fill E2 down into E3:E5
 
G

Guest

Thank You!

hgrove said:
TN.Jim wrote...
...
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."

With the following in A1:B20,

A 3
B 1
C 2
D 1
E 2
F 4
G 3
H 3
I 1
J 3
K 3
L 3
M 3
N 1
O 4
P 2
Q 2
R 4
S 1
T 1

Enter the following formula in D1.

D1:
=LARGE(B$1:B$20,ROW())

Fill D1 down into D2:D5. Then enter the following formulas.

E1:
=INDEX(A$1:A$20,MATCH(D1,B$1:B$20,0))

E2: [array formula]
=INDEX(A$1:A$20,MATCH(D2,B$1:B$20*(COUNTIF(E$1:E1,A$1:A$20)=0),0))

Then fill E2 down into E3:E5.
 
G

Guest

Can you explain the COUNTIF portion to me...
I understand the function but not how works in the array.
Thanks,
Jim

hgrove said:
TN.Jim wrote...
...
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."

With the following in A1:B20,

A 3
B 1
C 2
D 1
E 2
F 4
G 3
H 3
I 1
J 3
K 3
L 3
M 3
N 1
O 4
P 2
Q 2
R 4
S 1
T 1

Enter the following formula in D1.

D1:
=LARGE(B$1:B$20,ROW())

Fill D1 down into D2:D5. Then enter the following formulas.

E1:
=INDEX(A$1:A$20,MATCH(D1,B$1:B$20,0))

E2: [array formula]
=INDEX(A$1:A$20,MATCH(D2,B$1:B$20*(COUNTIF(E$1:E1,A$1:A$20)=0),0))

Then fill E2 down into E3:E5.
 
H

hgrove

TN.Jim wrote...
Can you explain the COUNTIF portion to me... ...
hgrove said:
E2: [array formula]
=INDEX(A$1:A$20,MATCH(D2,B$1:B$20*(COUNTIF(E$1:E1,A$1:A$20)=0),0))

E1 was your previously resulting name. A1:A20 is the range o
presumably distinct names. COUNTIF(E$1:E1,A$1:A$20) returns an arra
with 1s for those entries in A1:A20 with matches in E1 and 0
otherwise. When you fill the formula into E3, this call become
COUNTIF(E$1:E2,A$1:A$20), which returns an array with 1s for thos
entries in A1:A20 with matches in E1:E2 and 0s otherwise. So th
entries in the resulting array (COUNTIF(E1:E#,A$1:A$20)=0) are TRUE fo
names in A1:A20 that don't appear in E1:E#. That skips to the next name
 
G

Guest

Thanks again...
I appreciate the fact that you took the time to explain this to me...
It really helps to understand how a formula works so I can use it in the
future...


hgrove said:
TN.Jim wrote...
Can you explain the COUNTIF portion to me... ...
hgrove said:
E2: [array formula]
=INDEX(A$1:A$20,MATCH(D2,B$1:B$20*(COUNTIF(E$1:E1,A$1:A$20)=0),0))

E1 was your previously resulting name. A1:A20 is the range of
presumably distinct names. COUNTIF(E$1:E1,A$1:A$20) returns an array
with 1s for those entries in A1:A20 with matches in E1 and 0s
otherwise. When you fill the formula into E3, this call becomes
COUNTIF(E$1:E2,A$1:A$20), which returns an array with 1s for those
entries in A1:A20 with matches in E1:E2 and 0s otherwise. So the
entries in the resulting array (COUNTIF(E1:E#,A$1:A$20)=0) are TRUE for
names in A1:A20 that don't appear in E1:E#. That skips to the next name.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top