Combining multiple rows of data into one with conditions

ltensmeyer

So this question isn't as simple as it sounds. I have a list of patients and their medical insurance claims along with the description of their problem. What I need to do is go through all of this data and find each individual's top 2 conditions which they've made claims for. I've already used the summarize tool to count how many times each patient has made claims for the same problem. What i'm left with is something like this sample:

Last name	First name	DOB	Diagnosis	Diagnosis count
Doe	Jane	1/1/2000	Asthma	27
Doe	Jane	1/1/2000	Depression	22
Doe	Jane	1/1/2000	Covid	1
Doe	Jane	1/1/2000	Infection	1
Smith	John	1/5/2000	Sleep Apnea	13
Smith	John	1/5/2000	Unspecified Pain	2
Smith	John	1/5/2000	Prescriptions	2
Tank	Thomas	1/3/2000	Unspecified Pain	2

The result I need would be something like this:

Last name First name  DOB  Primary Diagnosis Secondary Diagnosis     Notes

Doe	Jane	1/1/2000	Asthma	Depression	Other diagnosis: Covid, infection
Smith	John	1/5/2000	Sleep Apnea	Unspecified pain	Other diagnosis: Prescription
Tank	Thomas	1/3/2000	Unspecified pain	Null	Null

 Any ideas are appreciated! (The notes section is ideal but isn't required, the two diagnosis are the more important part).

binu_acs

@ltensmeyer One way of doing this

Transpose_110923.yxmd