Vlookup duplicated number and return value that is associated most frequently

[Clutch1255]

Hello Forum Friends.

I'm sorry to have to ask, but I have exhausted the time I have to search existing threads for the answer I'm desperately looking for. I'm hoping someone can expeditiously return what I feel SHOULD be a rather simple solution.

Ultimately, I need the spreadsheet I'm working on to reflect which doctor (identified by his/her NPI) each patient (identified by his/her MRN) encountered most frequently.

Here's the set-up:

Column A: List of 7000 UNIQUE patient MRNs.

Column B: Currently blank, but will hopefully reflect which NPI each MRN is associated with most frequently in the subsequent table.

TABLE

Column C: MRNs from Column A, duplicated as many times as the associated patient was encountered by one of our affiliated doctors. So if a patient was seen 20 times, his/her MRN will appear in 20 rows.

Column D: The NPI associated with the doctor the patient encountered during each visit.

Here is a fictional example to illustrate the setup:

A) Patient MRNs (Unique List)	B) Doctor NPI Most Frequently Encountered	C) Patient MRNs (One Row/Encounter)	D) Doctor NPI (Encountered During Each Visit)
12345	879	12345	987
23456	321	12345	879
34567		12345	879
45678		12345	987
56789		12345	879
67890		12345	789
78901		23456	321
89012		23456	321
90123		23456	432

<tbody>
</tbody>

I'd like to use a simple Vlookup formula, but I can't figure out what I would need to add in order to search for ALL instances of each MRN and return the NPI that's associated the most number of times, rather than just searching for the initial instances of each MRN and returning whichever NPI happens to be associated with it.

I hope this explanation of what I'm hoping to accomplish/what data I have is clear.

Thank you for your time and attention thus far!

 

[shyy]

something like this where you return multiple instances from a single unique value?

Excel 2010

	F	G	H	I	J	K	L
45	Name	Location
46	Toronto	John Smith
47	Montreal	Jane Smith
48	Montreal	Kaitlin Lawrence
49	Montreal	Don Harvey
50	New York	Connie Doe		Toronto	John Smith
51	Paris	Cheryl Mackenzie		Montreal	Jane Smith	Kaitlin Lawrence	Don Harvey
52	New York	Sharon Argo		New York	Connie Doe	Sharon Argo
53	Leave	Jeff John		Paris	Cheryl Mackenzie
54	Singapore	Lori Bonnechance		Leave	Jeff John
55				Singapore	Lori Bonnechance
56

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Return Multiple Values​

Array Formulas Cell Formula I45 {=IFERROR(INDEX($I$30:$I$38,SMALL(IF($J$30:$J$38=I$42,ROW($J$30:$J$38)-ROW($J$30)+1),ROWS($I$43:I45))),"")} K45 {=IFERROR(INDEX($I$30:$I$38,SMALL(IF($J$30:$J$38=K$42,ROW($J$30:$J$38)-ROW($J$30)+1),ROWS($I$43:K45))),"")} L45 {=IFERROR(INDEX($I$30:$I$38,SMALL(IF($J$30:$J$38=L$42,ROW($J$30:$J$38)-ROW($J$30)+1),ROWS($I$43:L45))),"")} <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[yearnintolearn]

If you are looking for just the one single value as opposed to returning it all as was just posted this would work.

=MODE((INDIRECT("D"&MATCH(A1,C1:C7000,0)): (INDIRECT("D"&MATCH(A1,C1:C7000,0)+COUNTIF(C1:C7000,A1)))))

<tbody>
</tbody>

This formula of course assumes that your list in column C is sorted. Which if its not can be done simply with the filter function so as to keep the proper doctor mrn with the patient.

Also, you will need to change the C7000's to the proper length of your column C.

 

[Clutch1255]

Thanks for the response, Shyy.

So, rather than returning multiple values, I want it to return a single value: the single value that is associated with the searched value the most number of times.

Your example differs from mine because each person's name only shows up once.

In my example, the searched value and the associated values could be duplicated multiple times. I want to know which "returned" value each searched value is paired with the most number of times.

Does that make sense?

Thanks again!

 

[Clutch1255]

Yearnintolearn...I'm so glad that I asked. I NEVER could have cracked that code myself, but based on the tests I recently ran using your formula, it looks like I won't have to!

Awesome and very much appreciated!

Thank you sincerely!