Excel 2016 Macro Needed: Convert Multiple Columns into Multiple Rows

I have a spreadsheet (or many). I can move it into a template that is created. I am looking for an easy-to-use macro that is run by using a form or control button of some kind that will help transform date from many rows of multiple columns of data into multiple rows of data as show in the examples below. I am using Microsoft Excel 2016.

Here is what the multiple rows of data in columns which needs to change (BEFORE) with nearly 100 lines it looks like this (different data though) which needs to look like below (AFTER) after the macro is run.

I have attached a file that was previously created by help in this group. The macro is not working. I need to make sure that the file which can remain a template (it has data for testing/validation) that additional fields can be added in Row 1 as necessary.

1096      HRC2-CT-S      HRC2-OA-S      AFS-10U      CSH-USR      UVSG-42      FPKDB-S

1096 HRC2-CT-S
1096 HRC2-OA-S
1096 AFS-10U
1096 CSH-USR
1096 UVSG-43
1096 FPKDB-S
1097 etc
1097 etc
Tracy JohnsonAsked:
Who is Participating?
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Hi Tracy,

You don't need any macro to get the desired output.

To get the desired output, follow these steps....

1) Select any cell in the data, say A1.
2) Go to Data Tab and under Get & Transform Data, click on From Table/Range and click OK. This will open Power Query Editor with the data.
3) Right click on header KIT# and choose Unpivot Other Columns.
4) Click on Close & Load and the transposed data will be loaded on to a new sheet.

This is dynamic in nature i.e. if you add or delete data on Sheet1, you just need to right click the table created by Power Query and choose Refresh to update it, so no macros and no formulas.
Tracy JohnsonAuthor Commented:
Thank you. I have been out of pocket on a quick trip. I will check this out tomorrow morning.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Question answered.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.