6

I want to combine two lists in Google Spreadsheet so that the cells interleave.
Both lists have the same amount of elements.

So that:

1          A
2          B
3          C

becomes:

1
A
2
B
3
C

The closest I came up with is something like JOIN({List A},{List B}), but in this way the Elements of List B are only split by the first Element of List A.

It would be great if someone has an idea without any add-ons using only the built-in functions. Transposing and formatting is not that important.

4

Try:

=ArrayFormula(transpose(split(concatenate(A1:B&char(9)),char(9))))

(note that concatenate has a limit of 50000 characters)

or

=transpose(split(join(" ",query(transpose(A1:B),,50000))," "))

Where list A starts in A1, list B in B1.

2
  • 1
    Both suggestions worked just great. Thank you very, very much JPV! – Liam Jun 16 '15 at 12:51
  • Or with the undocumented function FLATTEN as =FLATTEN(A1:B). In brief: Flatten flattens the selected array to 1D. The array is stored in row-major (C-style) order which means that it will naturally stack row-wise. – FirefoxMetzger Dec 1 '20 at 7:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.