Convert rows of Excel table into one long column

Question

Please, can someone help me to save a couple of hours work?!

I need to take the cells in a table (stored left-to-right, up-to-down), and combine them all into one long vertical column.

This screenshot gives a simpler example:

Convert excel table into one column

The table I have to use is not in a logical order as this one. I don't want to lose hours on copying and pasting, but how?

Your desired action is called unpivot or reverse pivot, you can find a lot of resources online. — Máté Juhász, Jul 15 '16 at 11:43
Possible duplicate of [Transform multiple columns in Excel table into multiple rows](http://superuser.com/questions/1022794/transform-multiple-columns-in-excel-table-into-multiple-rows) — Scott - Слава Україні, Jul 15 '16 at 22:00

score 2 · Answer 1 · answered Jul 15 '16 at 12:15

2

Copy your table to clipboad, paste in Notepad and save the text file. Open the text file in MSWord, find for tab character and replace with manual enter.

This will flatten the contents to a single column and then you can copy them to Excel.

answered Jul 15 '16 at 12:15

Narayanan

835
2
10
15

thank you very much for your responses. Though I haven't solved it yet – user617500 Jul 15 '16 at 13:13
brilliant and smart resolution – Andrey Sarul Sep 25 '19 at 07:12

score 2 · Answer 2 · answered Jul 15 '16 at 19:30

you can use the free Microsoft Power Query Add-in (from Excel 2010) to transform the data range.

select the range
import as table to power query
select all column
on the transform ribbon, choose unpivot columns
select the value column, right click, remove other columns
load the table to Excel

here the Power Query script, which summarize the steps above.

let
    Source = Excel.CurrentWorkbook(){[Name="table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Columns",{"Value"})
in
    #"Removed Other Columns"

score 0 · Answer 3 · answered Jul 15 '16 at 12:23

0

This should work in your case of 8x8:

=INDEX($A$1:$H$8,ROUNDUP(ROW()/8,0),IF(MOD(ROW(),8)=0,8,MOD(ROW(),8)))

Alternately, if you have a table, you can just use the 'Convert to Range' function.

answered Jul 15 '16 at 12:23

Overmind

9,924
4
25
38

Convert rows of Excel table into one long column

3 Answers3