Listing multiple diseases for a single patient

MimiM · Jun 13, 2018

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.

Aladin Akyurek · Jun 13, 2018

Book1

A

B

C

D

E

F

G

H

I

J

1

2

Patient 1

Disease A

Practice X

Patient 1

Practice X

Disease A

Disease F

3

Patient 1

Disease F

Practice X

Patient 2

Practice Y

Disease B

Disease G

Disease H

4

Patient 2

Disease B

Practice Y

Patient 3

Practice Z

Disease A

Disease C

5

Patient 2

Disease G

Practice Y

6

Patient 2

Disease H

Practice Y

7

Patient 3

Disease A

Practice Z

8

Patient 3

Disease C

Practice Z

9

Sheet1

In E2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$8,SMALL(IF(FREQUENCY(IF(1-($A$2:$A$8=""),MATCH($A$2:$A$8,$A$2:$A$8,0)),ROW($A$2:$A$8)-ROW($A$2)+1),ROW($A$2:$A$8)-ROW($A$2)+1),ROWS($E$2:E2))),"")

In F2 just enter and copy down:

=IF($E2="","",VLOOKUP($E2,$A$2:$C$8,3,0))

In G2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(IF($E2="","",INDEX($B$2:$B$8,SMALL(IF($A$2:$A$8=$E2,ROW($A$2:$A$8)-ROW($A$2)+1),COLUMNS($G2:G2)))),"")

alansidman · Jun 13, 2018

another possible presentation is to use a Pivot Table as follows

Data Range
[Table="class: grid"][tr][td] [/td][td]

A

[/td][td]

B

[/td][td]

C

[/td][td]

D

[/td][td]

E

[/td][td]

F

[/td][td]

G

[/td][td]

H

[/td][td]

I

[/td][td]

J

[/td][td]

K

[/td][td]

L

[/td][/tr]
[tr][td]

1

[/td][td]

Column1

[/td][td]

Column2

[/td][td]

Column3

[/td][td]

Count of Column2

[/td][td]

Column Labels

[/td][td]

[/td][/tr]

[tr][td]

2

[/td][td]

Patient 1

[/td][td]

Disease A

[/td][td]

Practice X

[/td][td]

Row Labels

[/td][td]

Disease A

[/td][td]

Disease B

[/td][td]

Disease C

[/td][td]

Disease F

[/td][td]

Disease G

[/td][td]

Disease H

[/td][/tr]

[tr][td]

3

[/td][td]

Patient 1

[/td][td]

Disease F

[/td][td]

Practice X

[/td][td]

Patient 1

[/td][td]

[/td][/tr]

[tr][td]

4

[/td][td]

Patient 2

[/td][td]

Disease B

[/td][td]

Practice Y

[/td][td]

Practice X

[/td][td]

1

[/td][td]

1

[/td][td]

[/td][/tr]

[tr][td]

5

[/td][td]

Patient 2

[/td][td]

Disease G

[/td][td]

Practice Y

[/td][td]

Patient 2

[/td][td]

[/td][/tr]

[tr][td]

6

[/td][td]

Patient 2

[/td][td]

Disease H

[/td][td]

Practice Y

[/td][td]

Practice Y

[/td][td]

1

[/td][td]

1

[/td][td]

1

[/td][/tr]

[tr][td]

7

[/td][td]

Patient 3

[/td][td]

Disease A

[/td][td]

Practice Z

[/td][td]

Patient 3

[/td][td]

[/td][/tr]

[tr][td]

8

[/td][td]

Patient 3

[/td][td]

Disease C

[/td][td]

Practice Z

[/td][td]

Practice Z

[/td][td]

1

[/td][td]

1

[/td][td]

[/td][/tr]

[tr][td]

9

[/td][td]

[/td][/tr]

[tr][td]

10

[/td][td]

[/td][/tr]
[/table]

MimiM · Jun 13, 2018

Thank you, Aladin, will test and come back to you.

MimiM · Jun 17, 2018

Aladin's solution worked as needed, thanks again.

One step further now - how do I determine the most common combinations of diseases? There are over 16,000 patients with more than one disease recorded against them, from 2 to 13. I would be looking for, say, the top 5 combinations, e.g., Hypertension + Diabetes; Hypertension + Diabetes + Asthma. of course, the way the diseases come from the database in a different order, so the same one will not be in the same place for different patients. Ideally, I'd like to have them all ordered in the same way so that I can see the combinations somehow.

Is it possible at all?

Mimi

MimiM · Jun 17, 2018

And what I'm seriously struggling with is the combination of index-frequency. The explanations and examples of Frequency use somehow don't seem to fit...

Aladin Akyurek · Jun 18, 2018

MimiM said:
Aladin's solution worked as needed, thanks again.

Glad to help.

One step further now - how do I determine the most common combinations of diseases? There are over 16,000 patients with more than one disease recorded against them, from 2 to 13. I would be looking for, say, the top 5 combinations, e.g., Hypertension + Diabetes; Hypertension + Diabetes + Asthma. of course, the way the diseases come from the database in a different order, so the same one will not be in the same place for different patients. Ideally, I'd like to have them all ordered in the same way so that I can see the combinations somehow.

Is it possible at all?

Mimi

Care to elaborate what you mean using the earlier sample? Pretend 3 instead of 5...

MimiM · Jun 18, 2018

Imagine the following

Patient number Disease 1 Disease 2 Disease 3
1 Asthma Diabetes
2 Hypertension Asthma Diabetes
3 Diabetes Asthma
4 Diabetes Hypertension
5 Asthma Hypertension Chronic Kidney Failure
6 Hypertension Diabetes Asthma
And this in thousands of patients and up to 13 diseases

Are there any diseases that come up together more often? Which ones? are the questions that I am trying to answer.

From this very simple example, we could see that diabetes and hypertension come up together 3 times, so do diabetes and asthma, so do hypertension and asthma. The combination of the three is next down the list, etc.

It may be a question that is impossible to answer, I don't know.

Peter_SSs · Jun 18, 2018

Your question about the most common combinations seems to be a very complex one due to
a) You seem to be looking within the list of diseases for individual patients and
b) the order of diseases from the data source has no specific structure/order
This, together with the fact that you seem to have quite a big database, means that I suspect you will get no satisfaction from a formula solution.

I have a possible macro solution below but it still does not look within patient records for combinations but only at the full list for each patient. Given that, the macro will list the "top n" combinations. Change the value of n in the 'Const line near the top of the code.

To get to the result about most common combinations, the code also produces the list for each patient like the formula solution already suggested (so this may possibly replace that formula solution) but the macro lists the diseases for each patient in alphabetical order which may be of some use to you if still inspecting the results manually.

I have assumed that the data is in columns A:C and that there is nothing on the worksheet to the right of that.

In my small example, I have the code listing the diseases for each patient and also listing the top 3 combinations of diseases. Note that my result actually lists 4 combinations as there was a tie for the third most common combination.

Code:

Sub Analyse_Diseases()
  Dim d1 As Object, d2 As Object, AL As Object, SL As Object
  Dim a As Variant, itm As Variant
  Dim i As Long, nc As Long, k As Long, lastval As Long
  
  Const TopNum As Long = 3  '<- Top how many combinations to list
  
  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")
  a = Range("A2", Range("C" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    d1(a(i, 1) & ";" & a(i, 3)) = d1(a(i, 1) & ";" & a(i, 3)) & ";" & a(i, 2)
  Next i
  For i = 1 To d1.Count
    AL.Clear
    For Each itm In Split(Mid(d1.Items()(i - 1), 2), ";")
      AL.Add itm
    Next itm
    AL.Sort
    d1(d1.Keys()(i - 1)) = Join(AL.toarray, ";")
  Next i
  Range("E1:G1").Value = Array("Patient", "Practice", "Diseases")
  With Range("E2").Resize(d1.Count)
    .Value = Application.Transpose(d1.Keys)
    .TextToColumns DataType:=xlDelimited, Tab:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False
    With .Offset(, 2)
      .Value = Application.Transpose(d1.Items)
      .TextToColumns DataType:=xlDelimited, Tab:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False
    End With
  End With
  With Range("E1").CurrentRegion
    .Columns.AutoFit
    nc = .Column + .Columns.Count + 1
  End With
  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
  i = SL.Count
  Do While SL.GetKey(i - 1) <> "00000|x" And (k < TopNum Or Split(SL.GetKey(i - 1), "|")(0) - lastval = 0)
    Cells(Rows.Count, nc).End(xlUp).Offset(1).Resize(, 2).Value = Split(SL.GetKey(i - 1), "|")
    lastval = Split(SL.GetKey(i - 1), "|")(0)
    k = k + 1
    i = i - 1
  Loop
  With Cells(1, nc).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 sample data (columns A:C) and results of code (Columns E:L)

Book1

A

B

C

D

E

F

G

H

I

J

K

L

1

Patient

Disease

Practice

Patient

Practice

Diseases

Count

Disease Combination

2

Patient 1

Angina

Practice X

Patient 1

Practice X

Angina

Asthma

3

Measles;Mumps

3

Patient 1

Asthma

Practice X

Patient 2

Practice Y

Angina

Brain Cancer

Influenza

3

Angina;Brain Cancer;Influenza

4

Patient 2

Brain Cancer

Practice Y

Patient 3

Practice Z

Measles

Mumps

2

Asthma;Hypertension

5

Patient 2

Influenza

Practice Y

Patient 4

Practice X

Asthma

Hypertension

2

Angina;Asthma

6

Patient 2

Angina

Practice Y

Patient 5

Practice X

Angina

Asthma

7

Patient 3

Mumps

Practice Z

Patient 6

Practice X

Asthma

Hypertension

8

Patient 3

Measles

Practice Z

Patient 7

Practice X

Measles

Mumps

9

Patient 4

Asthma

Practice X

Patient 8

Practice X

Angina

Brain Cancer

Influenza

10

Patient 4

Hypertension

Practice X

Patient 9

Practice X

Measles

Mumps

11

Patient 5

Asthma

Practice X

Patient 10

Practice X

Angina

Brain Cancer

Influenza

12

Patient 5

Angina

Practice X

13

Patient 6

Hypertension

Practice X

14

Patient 6

Asthma

Practice X

15

Patient 7

Measles

Practice X

16

Patient 7

Mumps

Practice X

17

Patient 8

Influenza

Practice X

18

Patient 8

Angina

Practice X

19

Patient 8

Brain Cancer

Practice X

20

Patient 9

Measles

Practice X

21

Patient 9

Mumps

Practice X

22

Patient 10

Brain Cancer

Practice X

23

Patient 10

Influenza

Practice X

24

Patient 10

Angina

Practice X

Sheet1

Aladin Akyurek · Jun 18, 2018

MimiM said:
[…]It may be a question that is impossible to answer, I don't know.

Given the amount of data and the complexity of the computation that is required, a macro should just what you need.

Listing multiple diseases for a single patient

MimiM

New Member

Excel Facts

Aladin Akyurek

MrExcel MVP

alansidman

Well-known Member

MimiM

New Member

MimiM

New Member

MimiM

New Member

Aladin Akyurek

MrExcel MVP

MimiM

New Member

Peter_SSs

MrExcel MVP, Moderator

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

Listing multiple diseases for a single patient

New Member

Excel Facts

MrExcel MVP

Well-known Member

New Member

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP, Moderator

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock