How to organize the data by the variables?

Hello everyone,

I have a nagging Excel problem that no one is able to give me a solution. Hence, I am posting it here together with a sample excel file (my original file has a lot of data but if I can get it to work on this sample, I'll be able to apply it there as well). Here is the background of my problem.

I am designing schedule for residents in a hospital. They go on different rotations every week and for now the data is organized by names on the first column and weeks on the first row. Each cell lists the rotation e.g. Cardiology, Vacation, Nephrology, Hematology etc. This can be seen in the top half of the sheet

What I want to do is simply to reorganize the data so that the rotation names are in the first column followed by names of the residents in each week. The bottom half of the sheet is what I want to achieve. It will help me in that I can send a list of all the residents in each week to each department instead of writing it out manually (I have 57 residents, around 40 rotations and 54 weeks!). The individual departments (like cardiology, nephrology) don't want to sift through an entire chart of residents. What they want is a succinct list of resident names with their respective week.

Is there any way to do this? I tried pivot table or array formulas but to no avail. Can anyone come up with a simple formula or report or query of the data? I am willing to even try Microsoft Access if it can help in generating simple reports although I don't know how to use it.

Thank you so much!

I know this is not exactly what you want but maybe it will help

done with PowerQuery and PivotTable

Power Query for

• Excel 2010 Pro Plus or Excel 2013 Pro Plus can be downloaded as free add-in from MS site (choose correct version). I suggest Power Pivot also
• Excel 2016 or Excel 365 is built-in, aka Get&Transform
• PowerQuery works with M-language and does not contain any classic formulae or vba code
• What PowerQuery is? see here: Getting Started with PowerQuery (Get&Transform) in Excel

check if this is that what you want:
[Green table, done with PowerQuery (required)]