Digital Farmer
Digital Farmer

Reputation: 2107

Intercalate columns when they are in pairs

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.

Upvotes: 0

Views: 65

Answers (3)

player0
player0

Reputation: 1

you can do:

={FLATTEN({A1:A3, C1:C3}), FLATTEN({B1:B3, D1:D3})}

enter image description here

for more columns, it could be automated with MOD

Upvotes: 1

Martín
Martín

Reputation: 10092

You could try with MAKEARRAY

=MAKEARRAY(ROWS(A1:D3)*2,2,LAMBDA(r,c,INDEX(FLATTEN(A1:D3),c+(r-1)*2)))

enter image description here

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)

Upvotes: 1

rockinfreakshow
rockinfreakshow

Reputation: 29904

Well you can try:

=WRAPROWS(TOCOL(A1:D3),2)

enter image description here

Upvotes: 2

Related Questions