Row to Column Formatting for 5K Unique Rows

New Contributor


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!


screenshot1.jpgscreenshot 2.jpg

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

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) 

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

Thank you!

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies