Listing multiple diseases for a single patient

[MimiM]

Hi

I have an SQL extract table of patients with one or more diseases recorded for them. Each patient/disease information is a separate record/line. Through a pivot, I can see which patient has more than one disease recorded and which diseases are included. However, the pivot contains all possible diseases (columns) and all patients (rows).

My aim is to list each individual patient, with only the diseases recorded against them as columns. I would then try to summarise the combinations of diseases.

Any ideas, please?

Many thanks in advance

Mimi

Structure;
Patient 1 Disease A Practice X
Patient 1 Disease F Practice X
Patient 2 Disease B Practice Y
Patient 2 Disease G Practice Y
Patient 2 Disease H Practice Y
Patient 3 Disease A Practice Z
Patient 3 Disease C Practice Z

Aiming at

Patient 1 Practice X Disease A Disease F
Patient 2 Practice Y Disease B Disease G Disease H
Patient 3 Practice Z Disease A Disease C

etc.

 

[MimiM]

Thank you, Peter, I believe this should do it. Unfortunately, my VBA knowledge is nil. In order to make the function work, where do I need to change the code to reflect the following:

There are more than 3 columns in the raw data, but all I am interested in is the count of disease combinations - overall, not for each patient. I guess I can cut the data to two columns: patient and disease.

I have counted up to 13 diseases per patient

And - the silliest question - how do I get to write the macro? Just right-click on the sheet - view code - and then type/copy?

Thank you again, and I can only hope that, when I give the person some of this information, she won't come back for even more detail!

Mimi

 

[Peter_SSs]

There are more than 3 columns in the raw data,

1. But for this exercise are you only interested in 3 columns: Patient, Disease, & Practice?
- If so, which columns are they?
- If not, can you explain more about which columns are relevant and how/why?

2. Does the sheet have a fixed number of columns, or can it vary?

.. all I am interested in is the count of disease combinations - overall, not for each patient.

3. My code already gives the count of disease combinations overall, not per patient. For example, in my sample data the combination of Measles & Mumps occurred 3 times throughout the data altogether. Isn't that what you are wanting?

4. Do you still actually want the Practice & Diseases for each patient listed across a row like columns E:I in my sample or are you only interested in the combinations count like columns K:L?

And - the silliest question - how do I get to write the macro? Just right-click on the sheet - view code - and then type/copy?

It isn't a silly question - we all had to work that out the first time we came across macros, and you are close.
For this type of macro, after you right click and 'View Code', best to then use the menu in the vba window to Insert -> Module and paste the code in the right hand window that is created by that.
Then, to run the macro there are several ways but one is Alt+F8 to bring up the macro dialog, Select the 'Analyse_Diseases' macro & click 'Run'.
The workbook will need to be saved as a macro-enabled workbook (*.xlsm)

 

[MimiM]

1. But for this exercise are you only interested in 3 columns: Patient, Disease, & Practice?
- If so, which columns are they?
- If not, can you explain more about which columns are relevant and how/why?

2. Does the sheet have a fixed number of columns, or can it vary?


3. My code already gives the count of disease combinations overall, not per patient. For example, in my sample data the combination of Measles & Mumps occurred 3 times throughout the data altogether. Isn't that what you are wanting?

4. Do you still actually want the Practice & Diseases for each patient listed across a row like columns E:I in my sample or are you only interested in the combinations count like columns K:L?


It isn't a silly question - we all had to work that out the first time we came across macros, and you are close.
For this type of macro, after you right click and 'View Code', best to then use the menu in the vba window to Insert -> Module and paste the code in the right hand window that is created by that.
Then, to run the macro there are several ways but one is Alt+F8 to bring up the macro dialog, Select the 'Analyse_Diseases' macro & click 'Run'.
The workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Thank you again, Peter. Now, in order of questions

1. The columns I am interested in are Patient and Disease
2. I can limit the columns; the outputs have about 6, for other purposes
3. Your code is exactly what I am interested in: I was simply confirming that I am not necessarily interested in looking into each patient record
4. The overall question is about the total count; the listing per patient was my initial attempt at putting some order in the sea of data

And thank you for giving me the steps for the macro.

From reading the code, I am roughly guessing what it is doing and where I might amend, but wouldn't want to break anything. Sometimes Excel can be a bit temperamental, and it's an office computer/server.

Mimi

 

[Peter_SSs]

1. The columns I am interested in are Patient and Disease

Which columns are they? (eg Patient is col A and Disease is Col E)
Or can the columns vary and the code needs to look for the headings? If so, please give exact headings.

2. I can limit the columns; the outputs have about 6, for other purposes

There should be no need to do that. Results can just go off further to the right (the code can work that out) or the results could go on a new worksheet if you would prefer.

3. Your code is exactly what I am interested in: I was simply confirming that I am not necessarily interested in looking into each patient record
4. The overall question is about the total count; the listing per patient was my initial attempt at putting some order in the sea of data

OK, noted. When we resolve the above issues, we will just focus on the sort of results I had previously in columns K:L

 

[MimiM]

I'll need to go to that particular office and see what headings I'm pulling out of the database - this is tomorrow evening, Peter. As soon as I've got them, I'll let you know. Best to go by headings as I may decide to drop or add columns later on.

Funnily enough, in this workplace, a question has risen that may be very similar to the discussion above. Only here, it is about different types of activities, e.g., programs, counselling, online chats - again, the question is - how many people engage in what type of activities, and it seems the scope/combination is the focus of the question. I'll know more later. Might be able to adapt what you've suggested above - eventually.

Will come back tomorrow with the headings.

 

[Peter_SSs]

I'll need to go to that particular office and see what headings I'm pulling out of the database - this is tomorrow evening, Peter. As soon as I've got them, I'll let you know. Best to go by headings as I may decide to drop or add columns later on.

OK, you will just need to ensure that the headings in the code below match the headings in the worksheet. It doesn't matter what columns they are in or what order they are in. I have assumed that they will be in row 1 though.

I have also assumed that if there are multiple sheets in the workbook, the one with this data is the active one when the code is run.

This revised code also adds a new worksheet and puts the summary count on that new worksheet.
Edit the red code as required.

Sub Analyse_Diseases_v2()
  Dim d1 As Object, d2 As Object, AL As Object, SL As Object
  Dim a As Variant, ky As Variant, itm As Variant, aRws As Variant
  Dim i As Long, nr As Long, k As Long, LastVal As Long, lr As Long, ColPatient As Long, ColDisease As Long
  
  Const TopNum As Long = 3  '<- Top how many combinations you want to list
  
  With Rows(1)
    ColPatient = .Find(What:="Patient", LookAt:=xlWhole, MatchCase:=False).Column '<- Column heading to suit your data
    ColDisease = .Find(What:="Disease", LookAt:=xlWhole, MatchCase:=False).Column '<- Column heading to suit your data
  End With
  Set d1 = CreateObject("Scripting.Dictionary")
  d1.CompareMode = 1
  Set d2 = CreateObject("Scripting.Dictionary")
  d2.CompareMode = 1
  Set AL = CreateObject("System.Collections.ArrayList")
  Set SL = CreateObject("System.Collections.Sortedlist")
  lr = Cells(Rows.Count, ColPatient).End(xlUp).Row
  aRws = Evaluate("row(2:" & lr & ")")
  a = Application.Index(Cells, aRws, Array(ColPatient, ColDisease))
  For i = 1 To UBound(a)
    d1(a(i, 1)) = d1(a(i, 1)) & ";" & a(i, 2)
  Next i
  For Each ky In d1.keys()
    AL.Clear
    For Each itm In Split(Mid(d1(ky), 2), ";")
      AL.Add itm
    Next itm
    AL.Sort
    d1(ky) = Join(AL.ToArray, ";")
  Next ky
  For Each itm In d1.Items()
    d2(itm) = d2(itm) + 1
  Next itm
  For Each itm In d2.keys()
    SL.Add Format(d2(itm), "00000") & "|" & itm, 1
  Next itm
  SL.Add "00000|x", 1
  Sheets.Add After:=ActiveSheet
  Range("A1").Value = "Top " & TopNum
  nr = 2
  i = SL.Count
  Do While SL.GetKey(i - 1) <> "00000|x" And (k < TopNum Or Split(SL.GetKey(i - 1), "|")(0) - LastVal = 0)
    nr = nr + 1
    Cells(nr, 1).Resize(, 2).Value = Split(SL.GetKey(i - 1), "|")
    LastVal = Cells(nr, 1).Value
    k = k + 1
    i = i - 1
  Loop
  With Cells(2, 1).Resize(, 2)
    .Value = Array("Count", "Disease Combination")
    .CurrentRegion.Columns(1).TextToColumns DataType:=xlDelimited, Other:=False, FieldInfo:=Array(1, 1)
    .EntireColumn.AutoFit
  End With
End Sub

My new sample data:

Book1
	A	B	C	D	E
1	Patient	Other 1	Other 2	Disease	Practice
2	Patient 1	data	data	Angina	Practice X
3	Patient 1	data	data	Asthma	Practice X
4	Patient 2	data	data	Brain Cancer	Practice Y
5	Patient 2	data	data	Influenza	Practice Y
6	Patient 2	data	data	Angina	Practice Y
7	Patient 3	data	data	Mumps	Practice Z
8	Patient 3	data	data	Measles	Practice Z
9	Patient 4	data	data	Asthma	Practice X
10	Patient 4	data	data	Hypertension	Practice X
11	Patient 5	data	data	Asthma	Practice X
12	Patient 5	data	data	Angina	Practice X
13	Patient 6	data	data	Hypertension	Practice X
14	Patient 6	data	data	Asthma	Practice X
15	Patient 7	data	data	Measles	Practice X
16	Patient 7	data	data	Mumps	Practice X
17	Patient 8	data	data	Influenza	Practice X
18	Patient 8	data	data	Angina	Practice X
19	Patient 8	data	data	Brain Cancer	Practice X
20	Patient 9	data	data	Measles	Practice X
21	Patient 9	data	data	Mumps	Practice X
22	Patient 10	data	data	Brain Cancer	Practice X
23	Patient 10	data	data	Influenza	Practice X
24	Patient 10	data	data	Angina	Practice X
Data

Result after new code:

Book1
	A	B
1	Top 3
2	Count	Disease Combination
3	3	Measles;Mumps
4	3	Angina;Brain Cancer;Influenza
5	2	Asthma;Hypertension
6	2	Angina;Asthma
7
Sheet2

 

[MimiM]

This is wonderful, don't know how to thank you, Peter. Will try it tomorrow and keep for future reference

Mimi

 

[Peter_SSs]

This is wonderful,

You're welcome.

don't know how to thank you, Peter.

You just have.

Will try it tomorrow ..

Would be interested to know how it goes with your real (large) data. (I hope spelling in your system is consistent or results may not be reliable )

 

[MimiM]

This worked a treat - and was ever so fast!

[TABLE="width: 301"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Top 5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Count[/TD]
[TD] Disease Combination[/TD]
[/TR]
[TR]
[TD="align: right"]1677[/TD]
[TD] Diabetes Type 2;Hypertension[/TD]
[/TR]
[TR]
[TD="align: right"]559 [/TD]
[TD] Anxiety Exists;Depression Active[/TD]
[/TR]
[TR]
[TD="align: right"]438[/TD]
[TD] Hypertension;Osteoarthritis[/TD]
[/TR]
[TR]
[TD="align: right"]322[/TD]
[TD] Diabetes Type2;Hypertension;Osteoarthritis[/TD]
[/TR]
[TR]
[TD="align: right"]278[/TD]
[TD] Hypertension;Musculoskeletal Other[/TD]
[/TR]
</tbody>[/TABLE]

Ever so grateful!

 

[Peter_SSs]

This worked a treat - and was ever so fast!

[TABLE="width: 301"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Top 5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Count[/TD]
[TD] Disease Combination[/TD]
[/TR]
[TR]
[TD="align: right"]1677[/TD]
[TD] Diabetes Type 2;Hypertension[/TD]
[/TR]
[TR]
[TD="align: right"]559 [/TD]
[TD] Anxiety Exists;Depression Active[/TD]
[/TR]
[TR]
[TD="align: right"]438[/TD]
[TD] Hypertension;Osteoarthritis[/TD]
[/TR]
[TR]
[TD="align: right"]322[/TD]
[TD] Diabetes Type2;Hypertension;Osteoarthritis[/TD]
[/TR]
[TR]
[TD="align: right"]278[/TD]
[TD] Hypertension;Musculoskeletal Other[/TD]
[/TR]
</tbody>[/TABLE]

Ever so grateful!

You are very welcome. It was an interesting exercise for me.
On the face of it, looks like hypertension could be a particular focus.

Was this using the approximately 16,000 records that you had mentioned earlier?