COUNTIF & RANK by Name (Possibly involving MAX & INDEX)

[glubmink]

I am hoping someone on here might be able to assist. I'm having trouble developing a formula to rank, by name, doctors prescribing a certain drug in a wide list of drugs. Essentially, I am trying to identify the top 5 prescribers of a particular item ranked from 1-5 in descending order. Here is what I have:

Column C (Drugs), Column D (Quantity) & Column E (Doctors)
Trying to pull the top 5 prescribers of "*Hydroco/APAP*" from the data and rank them by name without the use of pivot tables and vlookup. Also trying to determine count of that item for each of the 5 and total quantity of that item.

Note: I am pulling data (approx. 40,000 lines) from Sheet1! to another sheet

RAW DATA
Column C Column D Column E
Hydroco/APAP Jim R. 90
Oxcodone Jim R. 90
Abilify Sherry T. 120
Hydroco/APAP Jim R. 120
Hydroco/APAP Pete M. 180
Zolpidem Roger T. 90
Hydroco/APAP Jim R. 120
Hydroco/APAP Melissa F. 30
Adderall Roger T. 30

This is what I want:
Column A Column B Column C
Name 1 Count of Hydroco/APAP Pill Count
Name 2 Count of Hydroco/APAP Pill Count
Name 3 Count of Hydroco/APAP Pill Count
etc.


Need help with pulling name rank, count of item by doc, and pill count of that item for each ranked doc


Hopefully this is possible.

 

[Aladin Akyurek]

Care to post the exact result set you want to get from the sample you posted?

 

[glubmink]

I apologize for the delay in response. I should have added another Pete M. Hydroco APAP to better illustrate what I am looking for. However, I would like to obtain the following result:

JIM R. / 3 (Count) / 330 (Qauntity)
PETE M. / 1 / 180
MELISSA F. / 1 (Count) / 30

Essentially, I want to rank the doctors by name based on how many times, by count, "*Hydroco/APAP*" appears next to their name. In the second column I want to show that count number in which "*Hydroco/APAP*" shows up next to each doctors name. Lastly, in the third column I would like to sum the quantity of pills attached to the count of "*Hydroco/APAP*", which is tied to that particular doctor. Does that make sense? I appreciate your assistance.

 

[Aladin Akyurek]

I apologize for the delay in response. I should have added another Pete M. Hydroco APAP to better illustrate what I am looking for. However, I would like to obtain the following result:

JIM R. / 3 (Count) / 330 (Qauntity)
PETE M. / 1 / 180
MELISSA F. / 1 (Count) / 30

Essentially, I want to rank the doctors by name based on how many times, by count, "*Hydroco/APAP*" appears next to their name. In the second column I want to show that count number in which "*Hydroco/APAP*" shows up next to each doctors name. Lastly, in the third column I would like to sum the quantity of pills attached to the count of "*Hydroco/APAP*", which is tied to that particular doctor. Does that make sense? I appreciate your assistance.

F3, control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},MIN($F$2,
   v(SUM(IF(SUMIFS($C$2:$C$10,$A$2:$A$10,$F$1,$B$2:$B$10,
   IF(FREQUENCY(MATCH($B$2:$B$10,$B$2:$B$10,0),
   ROW($B$2:$B$10)-ROW($B$2)+1),$B$2:$B$10)),1)))),
   SUM(IF(v()=LARGE(v(),$F$2),1))))

1. What this formula computes is required. That is, it tries to recompute N of Top N in case the Nth value whould have ties.
2. The foregoing computation avoids doing the same multiple times by using the V() function, which is a UDF in VBA...

You need to install this function in your workbook, using e.g., Alt+F11:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

E5, control+shift+enter, not just enter, and copy down:

=IF($G5="","",INDEX($B$2:$B$10,
   SMALL(IF(SUMIFS($C$2:$C$10,$A$2:$A$10,$F$1,$B$2:$B$10,
   IF(FREQUENCY(MATCH($B$2:$B$10,$B$2:$B$10,0),
   ROW($B$2:$B$10)-ROW($B$2)+1),$B$2:$B$10))=$G5,ROW($B$2:$B$10)-ROW($B$2)+1),
   COUNTIF($G$5:G5,G5))))

G5, control+shift+enter, not just enter, and copy down:

=IF(ROWS($G$5:G5)<=$F$3,LARGE(SUMIFS($C$2:$C$10,$A$2:$A$10,$F$1,$B$2:$B$10,
   IF(FREQUENCY(MATCH($B$2:$B$10,$B$2:$B$10,0),
   ROW($B$2:$B$10)-ROW($B$2)+1),$B$2:$B$10)),ROWS($G$5:G5)),"")

See the workbook that implements the foregoing set up: https://dl.dropboxusercontent.com/u/65698317/glubmink Top 5.xlsm.