Excel - Rows to Columns

OldManJenkin

Hi,

If I have a set of data formatted with data spread over 7 columns like this:

1  2  3  4  5  6  7
8  9  10 11 12 13 14
15 16 17...

Would anyone know how to turn this into one continuous column of data without manually copy/pasting or retyping the data out?

1
2
3
4
5
6
7
8
9
10
...

Thanks

Gurgle

Select Row-> Copy -> Click top cell of destination column -> Right Click -> Paste Special -> Transpose
-edit- oh, that won't be so easy as your data is in multiple rows and columns.

OldManJenkin

I found that option by searching, but as you said, it doesn't work for mutiple columns/rows .

TheNewMe

Try this

=INDEX($K$12:$Q$19,FLOOR((ROW(A1)-1)/7,1)+1,MOD(ROW(A1)-1,7)+1)

K12:Q19 defines the array, 7 is the length and width of the 2D array and this equation pulls out the first value. Just drag it down vertically and it should give you the full 2D array unrolled into a single 1D vector.

OldManJenkin

Thanks very much, worked brilliantly!
Very clever formula!

_AVALANCHE_

TheNewMe wrote: »

Try this

=INDEX($K$12:$Q$19,FLOOR((ROW(A1)-1)/7,1)+1,MOD(ROW(A1)-1,7)+1)

K12:Q19 defines the array, 7 is the length and width of the 2D array and this equation pulls out the first value. Just drag it down vertically and it should give you the full 2D array unrolled into a single 1D vector.