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:

[TABLE="width: 750"]
<tbody>[TR]
[TD]A) Patient MRNs (Unique List)[/TD]
[TD]B) Doctor NPI Most Frequently Encountered [/TD]
[TD]C) Patient MRNs (One Row/Encounter)[/TD]
[TD]D) Doctor NPI (Encountered During Each Visit)[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]879[/TD]
[TD]12345[/TD]
[TD]987[/TD]
[/TR]
[TR]
[TD]23456[/TD]
[TD]321[/TD]
[TD]12345[/TD]
[TD]879[/TD]
[/TR]
[TR]
[TD]34567[/TD]
[TD][/TD]
[TD]12345[/TD]
[TD]879[/TD]
[/TR]
[TR]
[TD]45678[/TD]
[TD][/TD]
[TD]12345[/TD]
[TD]987[/TD]
[/TR]
[TR]
[TD]56789[/TD]
[TD][/TD]
[TD]12345[/TD]
[TD]879[/TD]
[/TR]
[TR]
[TD]67890[/TD]
[TD][/TD]
[TD]12345[/TD]
[TD]789[/TD]
[/TR]
[TR]
[TD]78901[/TD]
[TD][/TD]
[TD]23456[/TD]
[TD]321[/TD]
[/TR]
[TR]
[TD]89012[/TD]
[TD][/TD]
[TD]23456[/TD]
[TD]321[/TD]
[/TR]
[TR]
[TD]90123[/TD]
[TD][/TD]
[TD]23456[/TD]
[TD]432[/TD]
[/TR]
</tbody>[/TABLE]

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
John Smith
Jane Smith	Kaitlin Lawrence	Don Harvey
Connie Doe	Sharon Argo
Cheryl Mackenzie
Jeff John
Lori Bonnechance

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]45[/TD]
[TD="bgcolor: #FFFFFF"]Name[/TD]
[TD="bgcolor: #FFFFFF"]Location[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]46[/TD]
[TD="bgcolor: #FFFFFF"]Toronto[/TD]
[TD="bgcolor: #FFFFFF"]John Smith[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]47[/TD]
[TD="bgcolor: #FFFFFF"]Montreal[/TD]
[TD="bgcolor: #FFFFFF"]Jane Smith[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]48[/TD]
[TD="bgcolor: #FFFFFF"]Montreal[/TD]
[TD="bgcolor: #FFFFFF"]Kaitlin Lawrence[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]49[/TD]
[TD="bgcolor: #FFFFFF"]Montreal[/TD]
[TD="bgcolor: #FFFFFF"]Don Harvey[/TD]
[TD="align: right"][/TD]

[TD="align: center"]50[/TD]
[TD="bgcolor: #FFFFFF"]New York[/TD]
[TD="bgcolor: #FFFFFF"]Connie Doe[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C0C0C0"]Toronto[/TD]

[TD="align: center"]51[/TD]
[TD="bgcolor: #FFFFFF"]Paris[/TD]
[TD="bgcolor: #FFFFFF"]Cheryl Mackenzie[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C0C0C0"]Montreal[/TD]

[TD="align: center"]52[/TD]
[TD="bgcolor: #FFFFFF"]New York[/TD]
[TD="bgcolor: #FFFFFF"]Sharon Argo[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C0C0C0"]New York[/TD]

[TD="align: center"]53[/TD]
[TD="bgcolor: #FFFFFF"]Leave[/TD]
[TD="bgcolor: #FFFFFF"]Jeff John[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C0C0C0"]Paris[/TD]

[TD="align: center"]54[/TD]
[TD="bgcolor: #FFFFFF"]Singapore[/TD]
[TD="bgcolor: #FFFFFF"]Lori Bonnechance[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C0C0C0"]Leave[/TD]

[TD="align: center"]55[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C0C0C0"]Singapore[/TD]

[TD="align: center"]56[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Return Multiple Values​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I45[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K45[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L45[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]

[/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[yearnintolearn]

If you are looking for just the one single value as opposed to returning it all as was just posted this would work.
[TABLE="width: 500"]
<tbody>[TR]
[TD]=MODE((INDIRECT("D"&MATCH(A1,C1:C7000,0)): (INDIRECT("D"&MATCH(A1,C1:C7000,0)+COUNTIF(C1:C7000,A1)))))
[/TD]
[/TR]
</tbody>[/TABLE]

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!