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

Patient	8:00	8:30	9:00	9:30	10:00
Pat1	Therap1	Therap2		Therap2
Pat2	Therap3		Therap1		Therap1
Pat3		Therap4	Therap2
Pat4	Therap3	Therap1		Therap2	Therap3

<tbody>
</tbody>

Result should look like this;
Tab Result
First row is header

Therapeut	Time	Patiënt
Therap1	8:00	Pat1
Therap1	8:30	Pat4
Therap1	9:00	Pat2
Therap1	10:00	Pat2
Therap2	8:30	Pat1
Therap2	9:00	Pat3
Therap2	9:30	Pat1, Pat4
Therap3	8:00	Pat2, Pat4
Therap3	10:00	Pat4
Therap4	8:30	Pat3

<tbody>
</tbody>

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!