0

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?

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. enter image description here

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

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

  1. select the range
  2. import as table to power query
  3. select all column
  4. on the transform ribbon, choose unpivot columns
  5. select the value column, right click, remove other columns
  6. 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"
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.