Match Greatest Number of Occurrences by Name. If Equal, Choose Most Recent

[jdppep]

I am trying to find a provider for a patient using the following conditions:

-if seen by multiple providers, choose the one with the most visits, then choose most recent visit

-if equal number of visits by providers, then choose most recent


This is the last formula I have started to work with:

=IF(AND(MAX(COUNTIF($K$2:$K$876,K2)),($M$2:$M$876="Newest")),"x","")

The problem with this, is it only considers those with "Newest" in column M. The formula needs to consider the conditions above, specifically, to choose the most visits, then most recent.


I am attempting to place an "x" in column N under "Select"


Here is a sample file:

https://docs.google.com/spreadsheet/ccc?key=0Atek2wjie2YLdC1WTENNME1Wb2xGZ1daV3lEaVpMYXc&usp=sharing

 

[Faseeh]

Hi jdppep,


If there are multiple providers, and you want to consider one with most visits, then on which date would you like to show "X". Please explain both cases with example.


Regards,

 

[jdppep]

Hi Faseeh,


If there are multiple providers, I want it to choose the provider with the most visits/occurrences, then the most recent visit date.


Ex. Rows 15-18: Patient #34567 had one visit to TEST, SAM and three visits to TEST, ZACHARY. The most recent of the four visits was to TEST, SAM on 6/9/2013. Because the patient was seen more frequently by TEST, ZACHARY, I want to identify him as the patient's primary provider using the most recent visit date with TEST, ZACHARY. Denoted by an "x" in cell N18.


If there are equal numbers of providers to the same patient, I want it to choose the most recent visit date.


Ex. Rows 22-25: Patient #65432 had two visits to TESTAEHR, DAVID and two visits to TEST, ZACHARY. I want it to choose the most recent date, which would be 5/9/2013 denoted by an "x" in cell N25


Thank you!

 

[Faseeh]

Hi jdppep,


Thank for your reply, but what in case there is a tie for max number, for example as i workout this problem, i came across:

[pre]

98765	8/13/2012	TESTAEHRPROD, JEFF	MSM
98765	3/25/2013	TESTAEHR, JACK	        MSM

[/pre]

..now there are 02 suppliers, that came only once, hence there is a tie, what should be done int his case?


Regards,

 

[Sajan]

Hello @jdppep,

Try the following array formula (shown for cell N2):

=IF(MAX(IF(ISNUMBER(MATCH(TRANSPOSE(IF(PatientID=E2,MATCH(Provider,Provider,0))), MODE.MULT(IF(PatientID=E2,MATCH(Provider,Provider,0)*{1,1})),0)), TRANSPOSE(VisitDate)))=F2, "X","")


enter with Ctrl + Shift + Enter (instead of Enter)


The workbook with the above formula is available at:

http://speedy.sh/6dffn/Chandoo-jdppep-Identify-Primary-Doctor.xlsx


Cheers,

Sajan.

 

[jdppep]

Faseeh,


In your example where there are two supplies with the same number of occurrences, I would want it to choose the most recent (3/25/13: TESTAEHR, JACK).


Sajan,


Thanks for the reply. I have entered the formula, using Ctrl + Shift + Enter, without success. The column returns blank.

 

[NARAYANK991]

Hi ,


Sajan has used the MODE.MULT function , which is available only in Excel 2010 and later.


Narayan

 

[NARAYANK991]

Hi ,


Will your data have more than 2 providers with the same number of visits ? For instance can a patient have 4 most frequent providers with the same number of visits ?


Narayan

 

[jdppep]

Hi Narayan,


The data could have more than 2 providers with the same number of visits. In this case, I would want it to choose the most recent visit.


Thank you!