Google Sheet - Transform two columns into one column using arrayformula (more than 50,000 characters)

4
votes

I'm using Google Sheets and looking for an arrayformula that able to take a list in two columns and arrange it alternately in one column. The sheet contains about 5,000 rows, each row has more than 35 characters.

enter image description here

I tried this:

=transpose(split(join(" ", query(transpose(B5:C),,50000)), " "))

But then I got this message:

enter image description here

Please take a look at the sheet here:

https://docs.google.com/spreadsheets/d/11T1Roj1trviOSiiTZS292-4l3oODid7KLi9oGz3Z66o/edit#gid=0

3
google-sheetsarray-formulasgoogle-sheets-query
Please see: stackoverflow.com/questions/22368544/… – Max Makhrov

3 Answers

3
votes

Assuming your 2 columns are A and B, this formula will "interlace" them:

=query(
  sort(
    {arrayformula({row(A1:A3)*2, A1:A3});
     arrayformula({row(B1:B3)*2+1, B1:B3})}
  ),
  "select Col2")

Screenshot of interlacing formula

Explanation, unwrapping the formula from the inside:

  1. Each value gets a unique number, based on its row number times 2 (+1 for the 2nd column)
  2. Everything is sorted based on this number
  3. Only the 2nd column is extracted for the result.
2
votes

EDIT: My sincere apologies, I did not read the question carefully enough. My response is incorrect.

This should work:

 ={B5:B12;C5:C12}

just be careful to NOT change it to

={B5:B;C5:C}

This will start an infinite loop where the spreadsheet will increase the amount of rows in the spreadsheet to allow this output column to expand, but in doing so increases the length of the 2 input columns, meaning the length of the output column increases even more, so the spreadsheet tries adding more rows, etc, etc. It'll make your sheet crash your browser or something each time you try to open it.

1
votes

In Row5:

=ArrayFormula(offset(B$5,INT((row()-5)/2),iseven(row())))

Would need to be copied down however.