Google Sheet - Transform two columns into one colu

问题:

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.

I tried this:

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

But then I got this message:

Please take a look at the sheet here:

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

回答1:

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")

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:

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.

回答3:

In Row5:

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

Would need to be copied down however.