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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.