Looking up all values in a table and returning each value's column header / row title in a separate row

[LouiseP]

[TABLE="width: 676"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]disp date[/TD]
[TD]lloyds[/TD]
[TD]homecare[/TD]
[TD]paracetamol[/TD]
[TD]aspirin[/TD]
[TD]omeprazole[/TD]
[TD]iron tabs[/TD]
[TD]sudafed[/TD]
[/TR]
[TR]
[TD]Patient A[/TD]
[TD="align: right"]01-09-17[/TD]
[TD]lloyds[/TD]
[TD][/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Patient B[/TD]
[TD="align: right"]03-09-17[/TD]
[TD][/TD]
[TD]homecare[/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Patient C[/TD]
[TD="align: right"]03-09-17[/TD]
[TD][/TD]
[TD]homecare[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD="align: right"]56[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Patient D[/TD]
[TD="align: right"]10-10-17[/TD]
[TD]lloyds[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]84[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Disp Date[/TD]
[TD]Pharmacy[/TD]
[TD]Drug[/TD]
[TD]Quantity[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Patient A[/TD]
[TD]01-09-17[/TD]
[TD]lloyds[/TD]
[TD]paracetamol[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Patient A[/TD]
[TD]01-09-17[/TD]
[TD][/TD]
[TD]paracetamol[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Patient B[/TD]
[TD]03-09-17[/TD]
[TD]homecare[/TD]
[TD]aspirin[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Patient B[/TD]
[TD]03-09-17[/TD]
[TD][/TD]
[TD]omeprazole[/TD]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Patient B[/TD]
[TD]03-09-17[/TD]
[TD][/TD]
[TD]sudafed[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Patient C[/TD]
[TD]03-09-17[/TD]
[TD]homecare[/TD]
[TD]paracetamol[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Patient C[/TD]
[TD]03-09-17[/TD]
[TD][/TD]
[TD]omeprazole[/TD]
[TD]56[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Patient C[/TD]
[TD]03-09-17[/TD]
[TD][/TD]
[TD]sudafed[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Patient D[/TD]
[TD]10-10-17[/TD]
[TD]lloyds[/TD]
[TD]aspirin[/TD]
[TD]84[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 619"]
<colgroup><col><col><col><col span="2"><col><col><col><col></colgroup><tbody></tbody>[/TABLE]
[TABLE="width: 619"]
<colgroup><col><col><col><col span="2"><col><col><col><col></colgroup><tbody></tbody>[/TABLE]

At the top is a table where each patient has multiple drugs listed with the quantity supplied for each. I need to be able to redisplay each drug as a separate row, also detailing which patient / date and only selecting the pharmacy against their 1st item.
I have found some formulas online, but they do not work if a quantity is repeated in the same row as per patient A.

Any help would be greatly appreciated.

 

[MartyS]

Does this help?


https://www.dropbox.com/s/f7d7gjin38qiqqz/DrugTable_Single.xlsx?dl=0

 

[LouiseP]

That looked great, however the main version has more than 5 drugs that can be selected - 38 at present.
The total number of different patients is nearly 500 - but normally only 100 selected each time I need to convert the data with 1 or 2 dispensing occurrences for each.

I have never used VBA so was hoping to solve using formulae. Sorry I couldn't add this extra detail to the original question

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/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][/tr][tr][td]

1​

[/td][td]Name[/td][td]disp date[/td][td]lloyds[/td][td]homecare[/td][td]paracetamol[/td][td]aspirin[/td][td]omeprazole[/td][td]iron tabs[/td][td]sudafed[/td][/tr]
[tr][td]

2​

[/td][td]Patient A[/td][td]

1/9/2017​

[/td][td]lloyds[/td][td][/td][td]

50​

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

50​

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

3​

[/td][td]Patient B[/td][td]

3/9/2017​

[/td][td][/td][td]homecare[/td][td][/td][td]

20​

[/td][td]

23​

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

6​

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

4​

[/td][td]Patient C[/td][td]

3/9/2017​

[/td][td][/td][td]homecare[/td][td]

100​

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

56​

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

30​

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

5​

[/td][td]Patient D[/td][td]

10/10/2017​

[/td][td]lloyds[/td][td][/td][td][/td][td]

84​

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

6​

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

7​

[/td][td]Idx[/td][td]Name[/td][td]Disp Date[/td][td]Pharmacy[/td][td]Drug[/td][td]Quantity[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

1​

[/td][td]Patient A[/td][td]

1/9/2017​

[/td][td]lloyds[/td][td]paracetamol[/td][td]

50​

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

9​

[/td][td]

1​

[/td][td]Patient A[/td][td]

1/9/2017​

[/td][td]lloyds[/td][td]omeprazole[/td][td]

50​

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

10​

[/td][td]

2​

[/td][td]Patient B[/td][td]

3/9/2017​

[/td][td]homecare[/td][td]aspirin[/td][td]

20​

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

11​

[/td][td]

2​

[/td][td]Patient B[/td][td]

3/9/2017​

[/td][td]homecare[/td][td]omeprazole[/td][td]

23​

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

12​

[/td][td]

2​

[/td][td]Patient B[/td][td]

3/9/2017​

[/td][td]homecare[/td][td]sudafed[/td][td]

6​

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

13​

[/td][td]

3​

[/td][td]Patient C[/td][td]

3/9/2017​

[/td][td]homecare[/td][td]paracetamol[/td][td]

100​

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

14​

[/td][td]

3​

[/td][td]Patient C[/td][td]

3/9/2017​

[/td][td]homecare[/td][td]omeprazole[/td][td]

56​

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

15​

[/td][td]

3​

[/td][td]Patient C[/td][td]

3/9/2017​

[/td][td]homecare[/td][td]sudafed[/td][td]

30​

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

16​

[/td][td]

4​

[/td][td]Patient D[/td][td]

10/10/2017​

[/td][td]lloyds[/td][td]aspirin[/td][td]

84​

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

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

=IF(ROWS($B$8:B8)>COUNT($E$2:$I$5),"",INDEX(ROW($A$2:$A$5)-ROW($A$2)+1,
    SMALL(IF(ISNUMBER($E$2:$I$5),ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($B$8:B8))))

In B8 just enter and copy down:

=IF($A8="","",INDEX($A$2:$A$5,$A8))

In C8 just enter and copy down:

=IF($A8="","",INDEX($B$2:$B$5,$A8))

In D8 just enter and copy down:

=IF($A8="","",LOOKUP(REPT("z",255),INDEX($C$2:$D$5,MATCH($B8,$A$2:$A$5,0),0)))

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

=IF($A8="","",IFERROR(INDEX($E$1:$I$1,SMALL(IF(ISNUMBER(INDEX($E$2:$I$5,MATCH($B8,$A$2:$A$5,0),0)),
     COLUMN($E$1:$I$1)-COLUMN($E$1)+1),COUNTIFS($B$8:B8,B8))),""))

In F8 just enter and copy down:

=IF($A8="","",VLOOKUP($B8,$A$1:$I$5,MATCH($E8,INDEX($A$1:$I$5,1,0),0),0))

 

[LouiseP]

Thank you. that worked perfectly - have managed to transfer to the master sheet and does what it should so saving me lots of time doing it manually

 

[Aladin Akyurek]

Thank you. that worked perfectly - have managed to transfer to the master sheet and does what it should so saving me lots of time doing it manually

Glad to help. Thanks for the update.