Horizontal data to vertical columns

[2vbr]

The type of spreadsheets I get:

		A	B	C	D	E	F	G	H	I	J
0001	ONE			1	1
0002	TWO									1	1
0003	THREE					1	1	1	1
0004	FOUR					1	1	1	1
0005	FIVE	1

What I need to turn it into:

0001	ONE	C	1
0001	ONE	D	1
0002	TWO	I	1
0002	TWO	J	1
0003	THREE	E	1
0003	THREE	F	1
0003	THREE	G	1
0003	THREE	H	1
0004	FOUR	E	1
0004	FOUR	F	1
0004	FOUR	G	1
0004	FOUR	H	1
0005	FIVE	A	1

What I do currently do is time-consuming, and I'm looking for ideas on what could make it faster.

To summarize my current method:

1. Duplicate all the cells with 1's in them, then replace those cells with an entire string of data, seperated by a period ("001.ONE.C.1").
2. Highlight and name the range.
3. Transpose all the data into a single column.
4. Use the Text-To-Columns feature to split the data using the periods.

Problem is, it takes quite a long time (especially the 1st step; using a formula like =$A2&"."&$B2&"."&E$1&"."&E2 and copying that formula into every cell with a 1 in it).

I would like to ask an Excel expert to think of a faster automated way, but the problem is I don't know what to ask.

"Can you automate this process using VBA?"

"Can you automate this process using macros?"

"Can you automate this process using a purchased program?"

I just want to make sure I ask the right questions and not say the wrong words like an idiot. Best case scenario, I'd like to be able to tell other people "To convert this horizontal table to vertical, just use your mouse to highlight all the data, then press F11. Excel will do the rest."

Does VBA do that? Does a purchasable program you know do that? I don't mind spending money on a program or hiring a freelance IT expert to make something, but I need to know the right words to use when describing my problem and desired solution. I don't want to sound like the cliche clueless coworker who asks the IT staff "Can we use our email program as a cloud host?" or something similarly eye-rolling.

[AliGW]

You can do this using the free Power Query add-in from Microsoft.

[2vbr]

I downloaded this FREE addon, and after a quick "How To Convert Columns To Rows In Excel With Power Query" google search and some playing around... I DID IT!

All by myself! <----- a BIG deal!

Thankyou SO much! This turned an almost 20 minute complex manual job into 1 minute. WOW!

[AliGW]

Yes, it's a great find, and it's in newer versions of Excel as standard.