Combine to STACK multiple columns in one column.

[alimbondre]

Dear Experts,
In Excel, I have data in 10 columns (Lets say; A, B, C, D, E, F, G, H, I & J) starting from A1 to A1000 or more, B1 to B1000 or more, etc etc. Can I get all this data in any other Column (Lets say Column k) such that all data in Column A is on top, followed by all data in Column B, then C, D and finally J at the last. This also means that if there is data in all 1000 rows of each (A, B, C, D, E, F, G, H, I & J) then Column K will show data from K1 to K10,000. Blanks, Zeros, #REF, etc in A, B, C, D, E, F, G, H, I & J must be ignored in K.

There was a thread in 2010 requiring only 2 columns (explained by Mr. Marcelo Branco) but I am unable to edit that formulae to get all 10 Columns. Can someone help achieve this without a VBA.

Thanks.

 

[gordsky]

If you already have a formula that works and just needs expanding then perhaps paste it so others can adapt for you

 

[alimbondre]

This is the link where Mr. Marcelo Branco has given detailed and step by step explanation on how to stack data from TWO columns into one.

Stacking multiple columns into one column without including 0 or Blanks

Column A Column B Column C Program X Program Y Stack 12345 54321 12345 23451 43215 23451 34512 32154 34512 45123 21543 45123 FMPU 0 FMPU 0 0 54321 43215 0 0 32154 0 21543 Is there a way, formula or VBA, to stack column A and B and have it...

www.mrexcel.com

 

[gordsky]

using that method you would have to add the cell E2 formula for each of your columns from col L onwards for the number of columns you have (changing a to b and so on)
you would then need to amend the array formula to add another section for each of your columns.

there is a suggestion in that thread to use power query and table to columns. I would think that would be the quickest way to go.

 

[alimbondre]

@gordsky Yes, but the issue is that I am unable to amend and add extra columns to that formula. However, still trying. Thanks.