Copy data in column from each row

[ellyzadg]

Hi Everyone,

Would like to ask an easy way where I can easily list the information in rows into column list.
Thanks!

Current Data
In actual, this composed of around100 data rows
Note:

	Column A	Column B	Column C	Column D	Column E	Column F	Column G	Column H
Row 1	Order1	APPLE	BANANA	CAT	DOG	EEL	GOAT	HAT
Row 2	Order2	APPLE-batch1	BANANA	CAT	DOG	EEL	GOAT	HAT
Row 3	Order3	APPLE	BANANA	CAT	DOG	EEL

EXPECTED RESULT

	Column A	Column B
Row 1	Order1	APPLE
Row 2	Order1	BANANA
Row 3	Order1	CAT
Row 4	Order1	DOG
Row 5	Order1	EEL
Row 6	Order1	GOAT
Row 7	Order1	HAT
Row 8	Order2	APPLE-batch1
Row 9	Order2	BANANA
Row 10	Order2	CAT
Row 11	Order2	DOG
Row 12	Order2	EEL
Row 13	Order2	GOAT
Row 14	Order2	HAT
Row 15	Order3	APPLE
Row 16	Order3	BANANA
Row 17	Order3	CAT
Row 18	Order3	DOG
Row 19	Order3	EEL

 

[Alex Blakenburg]

That is really easy to do in Power Query if you are happy to go that way.

 

[ellyzadg]

That is really easy to do in Power Query if you are happy to go that way.

Thank you for the suggestion. I think that would be nice however, I still do not how to use power query

 

[Alex Blakenburg]

I can try to talk you through it.

• Convert your original data table into a proper Excel table.
• Give the table a name. I called mine tblData
• Data > From Table/Range (button on far left)
• You will now be in the Power Query editor
• If you see a Changed Type step after Source in the right hand pane - delete it
• Select your first column (Column A)
• Transform > Unpivot Columns > Unpivot Other Column
• Click anywhere in the grid and hit Ctrl+A (select all)
• Transform > Detect Data Type (will be roughly below Add Column).
• In the right hand side give the query a name it will become the output table name too, so pick something useful
• Home > Close & Load

You will then have a table in Excel in the format you are after.

It sounds like a lot of steps but in Power Query it is only 3 steps.