I am working with a large table and need to transpose many rows of data into one column.
Ex:
I need to go from this:
1 2 3 4 5
6 7 8 9 1
2 3 4 5 6
...
To this:
1
2
3
4
5
6
7
8
9
1
2
3
4
5
6
...
Can this be done easily with a function?
I am working with a large table and need to transpose many rows of data into one column.
Ex:
I need to go from this:
1 2 3 4 5
6 7 8 9 1
2 3 4 5 6
...
To this:
1
2
3
4
5
6
7
8
9
1
2
3
4
5
6
...
Can this be done easily with a function?
It can be done with a function. It depends on where your starting point is. Assuming that the information you have starts in B1, and goes across then down, and assuming that you want the list in its final format as described in cell A1 down, you can use this formula:
=OFFSET($B$1,INT(ROW()-1)/5,MOD(ROW()-1,5))
Thanks BigBas,
works great! although I'm not sure I completely understand the function, you just save me a lot of work.
Glad I could help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks