Row to Column Formatting for 5K Unique Rows

[sharonb]

Hello,

What I'm trying to do seems simple but not when I try to 5K unique IDs. I have 5k hospitals all in row format. Each hospital has procedural volumes (counts) for 33 different procedures, with the procedures in column format. Example in first screenshot below. 

What I need to do is convert the rows to columns, but have that repeat for the 5K unique hospitals and 33 unique procedures with the procedures now in rows. Result would be example in screenshot 2 below. Any suggestions? Thank you!

[#2]

Hi! I will try something for you... in meantime pls check if you can make rirdt table into pivot table.

[#3]

Hi! I will try something for you... in meantime pls check if you can change first table into pivot table.

[#4]

Thank you!! 

Yes, I have tried creating a Pivot Table on the original file, but what I cannot figure out from there is how to get the 33 procedures repeating down the one column I need (Procedures Column) 

[#5]

Hi Sharonb, sorry- doesn’t work with pivots. A vba procedure will work. Pls. Check this site for excel vba best practice: www.yexcel.de, they also got many vba tutorials and scenarios in english, French, Spanish... hope that helps. Greets, Eva

[#6]

Thank you!

[#7]

With Power Query that is two steps job - you query your table, select first two columns, unpivot other columns and return result back to Excel sheet.

Please see in attached  file.