Using this table:
| A | B | C | D |
|---|---|---|---|
| 1 | 2 | 3 | 4 |
| 5 | 6 | 7 | 8 |
| 9 | 10 | 11 | 12 |
In Google Sheets if I do this here in column E:
={A1:B3;C1:D3}
Teremos:
| E | F |
|---|---|
| 1 | 2 |
| 5 | 6 |
| 9 | 10 |
| 3 | 4 |
| 7 | 8 |
| 11 | 12 |
But the result I want is this:
| E | F |
|---|---|
| 1 | 2 |
| 3 | 4 |
| 5 | 6 |
| 7 | 8 |
| 9 | 10 |
| 11 | 12 |
I tried multiple options with FLATTEN, but none of them returned what I wanted.
CodePudding user response：
Well you can try:
=WRAPROWS(TOCOL(A1:D3),2)
CodePudding user response：
You could try with MAKEARRAY
=MAKEARRAY(ROWS(A1:D3)*2,2,LAMBDA(r,c,INDEX(FLATTEN(A1:D3),c (r-1)*2)))
GENERAL ANSWER
For you or anyone else: to do something similar but with a variable number of columns of origin or of destination, you can use this formula. Changing the range and amount of columns at the end of LAMBDA:
=LAMBDA(range,cols,MAKEARRAY(ROWS(range)*ROUNDUP(COLUMNS(range)/cols),cols,LAMBDA(r,c,IFERROR(INDEX(FLATTEN(range),c (r-1)*cols)))))(A1:D3,2)
CodePudding user response：
you can do:
={FLATTEN({A1:A3, C1:C3}), FLATTEN({B1:B3, D1:D3})}
for more columns, it could be automated with MOD