Transpose multiple rows to a single column in Libre Office

Question

I have a set of data 245 rows deep by 28 columns wide. I want to move each row of data into a single column where each row of data is then vertically stacked above the row below. I can't just use Copy,Paste,Transpose as it leaves me with a lot of manual work moving the 245 columns into one.

e.g. from:
A B C D
1 2 3
4 5 6
7 8 9

to:
A
1
2
3
4
5
6
7
8
9

Any help on this would be much apprecicated!

Answer 1

I ended up transposing in a text editor like sublime, pycharm :'(

However there is also:

• https://help.libreoffice.org/6.3/en-US/text/scalc/guide/table_rotate.html?DbPAR=CALC

Answer 2

Assuming your data is in Sheet1 and your results shall be in Sheet2. Enter the following formula in Sheet2.A1 and fill down as needed.

=INDEX($Sheet1.$A$1:$AMJ$1000,INT((ROW(A1)-1)/COUNTA($Sheet1.$A$1:$AMJ$1))+1,MOD((ROW(A1)-1),COUNTA($Sheet1.$A$1:$AMJ$1))+1)

Maybe you have to use semicolon instead comma as formula separator:

=INDEX($Sheet1.$A$1:$AMJ$1000;INT((ROW(A1)-1)/COUNTA($Sheet1.$A$1:$AMJ$1))+1;MOD((ROW(A1)-1);COUNTA($Sheet1.$A$1:$AMJ$1))+1)

How it works:

Basicly it is a normal INDEX(range,row,column).

The row part is INT((ROW(A1)-1)/COUNTA($Sheet1.$A$1:$AMJ$1))+1. Assuming you have 10 cells filled in row 1 from A1:J1. Then the COUNTApart results in 10. Then it calculates the integer part of 0/10, 1/10, 2/10,..., 10/10, 11/10,... depending on how much down you fill the formula because the A1 in ROW(A1) changes to A2, A3,... This is 10 times 0, then 10 times 1, then 10 times 2,... Then it added 1. So we have 10 times row 1, then 10 times row 2, ...

The columnpart is MOD((ROW(A1)-1);COUNTA($Sheet1.$A$1:$AMJ$1))+1. Assuming again, the COUNTApart results in 10. Then it calculates the residual of 0/10, 1/10, 2/10,..., 9/10, 10/10,11/10,... depending on how much down you fill the formula. So, added with 1, we have 1,2,3,...10,1,2,3,...10,...