Transpose multiple columns with multiple rows into one column

[trizzle2131]

I have many columns of data that need to be transposed into one column. I'll try to describe an example: A1:A10 has the value 5190 in each cell, B1:B10 has 5191 in each cell, C1:C10 has 5192 in each cell, and so forth. There are many, many columns like this that need to be transposed into one column so A1:A10 would be 5190, A11:A20 would be 5191, A21:A30 would be 5192, and so on. I'm struggling to find an efficient way to tackle this. I have tens of thousands of observations, so cutting and pasting is extremely tedious.

Any help is greatly appreciated. Thanks!

[XOR LX]

Hi.

In your example each of those ranges consists of precisely the same number of rows. What's more, their columns are contiguous.

Can you confirm whether this is always the case? If not, can you give a more realistic example?

Regards

[trizzle2131]

Yes, there will always be the same number of rows and the columns will always be contiguous. The columns will not, however, always be a perfect series. For example, A1:A10 may consist of 5101, but B1:B10 jumps to 5105.

[XOR LX]

Thanks.

So, assuming your range is e.g. A1:D10 and that every single one of the entries within that range is to be returned into your new list, put the following formula (used to determine the number of returns) in E1:

=COUNTA(A1:D10)

and then the following formula in your first cell of choice:

=IF(ROWS($1:1)>$E$1,"",INDEX($A$1:$D$10,1+MOD(ROWS($1:1)-1,ROWS($A$1:$D$10)),CEILING(ROWS($1:1)/ROWS($A$1:$D$10),1)))

Copy this formula down (though not the one in E1 - that's a one-off) until you start to get blanks for the results.

Regards

[trizzle2131]

Perfect! Thanks for the help.