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).

[binuacs · Accepted solution]

@ltensmeyer One way of doing this

[ScottAMccoy]

So, the best way I found to accomplish this is to Use another Summarize and to Group By Last Name, First Name, and DOB with a Concatenate on the Diagnosis. Then split the Diagnosis to separate columns, via a Text to Column with the number of columns set to 3 and Extra characters set to Leave extra in last column. Other methods using Cross Tab might also work but could run into issues with the Notes column. After that I just added a Formula to add in the "Other diagnosis:" when there existed more than two. A Select at the very end cleans up the extra column and rename columns to those in your example output.

[ltensmeyer]

This looks great! I'm having trouble opening your file but I might be able to recreate it just based on your description.

[ltensmeyer]

@binuacs Thank you! My actual dataset has about 500 patients and I'm not sure if I can get them all organized enough for this method to work but I'm trying it now!

[Paridhi_Agrawal · Accepted solution]

Hi,

Check the attached workflow if it helps.