Retrieve values in multiple rows and columns

[JaSc68]

Hi all,

i've got a worksheet "patiënts" where in each row fore some columns (hours) a therapist is defined.
Now i want to generate a new tab where i can see for each therapist the time and the patiënts he/she needs to treat.
From ther i can generate a planning overview
If there is a moment where the therapist needs to treat 2 or 3 patiënts in group, those patiënts (multiple values) should be put in one cell.
I need to work with +/- 300 patiënts and +/- 30 therapists.
Is there anyone who knows how can i achieve this result?
I've put a simple example below.

Tab Patiënts;
First row is header
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Patient
[/TD]
[TD]8:00
[/TD]
[TD]8:30
[/TD]
[TD]9:00
[/TD]
[TD]9:30
[/TD]
[TD]10:00
[/TD]
[/TR]
[TR]
[TD]Pat1
[/TD]
[TD]Therap1[/TD]
[TD]Therap2[/TD]
[TD][/TD]
[TD]Therap2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pat2
[/TD]
[TD]Therap3[/TD]
[TD][/TD]
[TD]Therap1[/TD]
[TD][/TD]
[TD]Therap1[/TD]
[/TR]
[TR]
[TD]Pat3
[/TD]
[TD][/TD]
[TD]Therap4[/TD]
[TD]Therap2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pat4
[/TD]
[TD]Therap3[/TD]
[TD]Therap1[/TD]
[TD][/TD]
[TD]Therap2[/TD]
[TD]Therap3[/TD]
[/TR]
</tbody>[/TABLE]

Result should look like this;
Tab Result
First row is header
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Therapeut
[/TD]
[TD]Time[/TD]
[TD]Patiënt[/TD]
[/TR]
[TR]
[TD]Therap1[/TD]
[TD]8:00[/TD]
[TD]Pat1[/TD]
[/TR]
[TR]
[TD]Therap1[/TD]
[TD]8:30[/TD]
[TD]Pat4[/TD]
[/TR]
[TR]
[TD]Therap1[/TD]
[TD]9:00[/TD]
[TD]Pat2[/TD]
[/TR]
[TR]
[TD]Therap1[/TD]
[TD]10:00[/TD]
[TD]Pat2[/TD]
[/TR]
[TR]
[TD]Therap2[/TD]
[TD]8:30[/TD]
[TD]Pat1[/TD]
[/TR]
[TR]
[TD]Therap2[/TD]
[TD]9:00[/TD]
[TD]Pat3[/TD]
[/TR]
[TR]
[TD]Therap2[/TD]
[TD]9:30[/TD]
[TD]Pat1, Pat4[/TD]
[/TR]
[TR]
[TD]Therap3[/TD]
[TD]8:00[/TD]
[TD]Pat2, Pat4[/TD]
[/TR]
[TR]
[TD]Therap3[/TD]
[TD]10:00[/TD]
[TD]Pat4[/TD]
[/TR]
[TR]
[TD]Therap4[/TD]
[TD]8:30[/TD]
[TD]Pat3[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance!

 

[pecosvil]

That's call "reshape" in other software like Stata (you are reshaping the dataset from wide to long). You might want to have a quick Google Search, probably there are some Stata users that already tried to do a reshape directly in Excel.
See for instance this:
http://www.pcreview.co.uk/forums/reshape-excel-t2754202.html

Let us know if you had success!