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

LouiseP

New Member
Joined
Feb 19, 2018
Messages
3
Namedisp datelloydshomecareparacetamolaspirinomeprazoleiron tabssudafed
Patient A01-09-17lloyds5050
Patient B03-09-17homecare20236
Patient C03-09-17homecare1005630
Patient D10-10-17lloyds84
NameDisp DatePharmacyDrugQuantity
Patient A01-09-17lloydsparacetamol50
Patient A01-09-17paracetamol50
Patient B03-09-17homecareaspirin20
Patient B03-09-17omeprazole23
Patient B03-09-17sudafed6
Patient C03-09-17homecareparacetamol100
Patient C03-09-17omeprazole56
Patient C03-09-17sudafed30
Patient D10-10-17lloydsaspirin84

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col span="2"><col><col><col><col></colgroup><tbody></tbody>

<colgroup><col><col><col><col span="2"><col><col><col><col></colgroup><tbody></tbody>

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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
 
Upvote 0
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
I​
1​
Namedisp datelloydshomecareparacetamolaspirinomeprazoleiron tabssudafed
2​
Patient A
1/9/2017
lloyds
50
50
3​
Patient B
3/9/2017
homecare
20
23
6
4​
Patient C
3/9/2017
homecare
100
56
30
5​
Patient D
10/10/2017
lloyds
84
6​
7​
IdxNameDisp DatePharmacyDrugQuantity
8​
1​
Patient A
1/9/2017
lloydsparacetamol
50
9​
1​
Patient A
1/9/2017
lloydsomeprazole
50
10​
2​
Patient B
3/9/2017
homecareaspirin
20
11​
2​
Patient B
3/9/2017
homecareomeprazole
23
12​
2​
Patient B
3/9/2017
homecaresudafed
6
13​
3​
Patient C
3/9/2017
homecareparacetamol
100
14​
3​
Patient C
3/9/2017
homecareomeprazole
56
15​
3​
Patient C
3/9/2017
homecaresudafed
30
16​
4​
Patient D
10/10/2017
lloydsaspirin
84

In A8 control+shift+enter, not just enter, and copy down:
Rich (BB code):
=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:
Rich (BB code):
=IF($A8="","",INDEX($A$2:$A$5,$A8))

In C8 just enter and copy down:
Rich (BB code):
=IF($A8="","",INDEX($B$2:$B$5,$A8))

In D8 just enter and copy down:
Rich (BB code):
=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:
Rich (BB code):
=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:
Rich (BB code):
=IF($A8="","",VLOOKUP($B8,$A$1:$I$5,MATCH($E8,INDEX($A$1:$I$5,1,0),0),0))
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,743
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top