5

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

braaterAfrikaaner
  • 1,072
  • 10
  • 20
Eli
  • 91
  • 2
  • 7
  • 1
    Please see: https://stackoverflow.com/questions/22368544/new-google-spreadsheet-concatenate-limit-50000-characters – Max Makhrov Feb 05 '18 at 11:12

4 Answers4

3

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.
ttarchala
  • 4,277
  • 2
  • 26
  • 36
3

There is a function for this called FLATTEN(). This works perfectly as a general solution since it takes an array of any size and outputs the items in the order they appear left-right-top-down (See here). It can be combined with TRANSPOSE() to accomplish the same thing but in the horizontal case, and if needed blank cells can be omitted with FILTER().

Gabriel S
  • 31
  • 1
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.

Quaris
  • 361
  • 2
  • 9
  • An infinite loop? That would be quite weird, I've never seen Google Spreadsheets expanding just because the user addressed rows wrong. I cannot replicate this behavior. Can you share a spreadsheet where it occurs? – ttarchala Feb 07 '18 at 10:19
  • 1
    Yes, array formulas can create row-adding feedback loops that are only stopped by the 50,000 cell limit. In [this sheet](https://docs.google.com/spreadsheets/d/1aATd7hiL6bCIrAbkNGmbnYLOIG_iWfj5MGLMPPssDpk/edit) cell C2 is set to the string `arrayformula(A1:A+B1:B)`. If the formula is activated with an = (`=arrayformula(A1:A+B1:B)`) then rows suddenly start expanding and will keep expanding until they hit the 50,000 row limit. – Quaris Feb 07 '18 at 15:25
  • 1
    I have noticed the desktop I'm using now doesn't have as much problem handling a 50,000 - row spreadsheet as my usual laptop, so the consequences probably depend on your computer. – Quaris Feb 07 '18 at 15:28
  • 1
    Thanks! Looks like you discovered a bug in GSheets. Still I'm afraid your solution does not solve the problem of the OP. It will copy one column after another, instead of interlacing them. – ttarchala Feb 07 '18 at 16:51
  • 1
    The adding row loop can be resolved by using a Filter : `=FILTER({A1:A;B1:B}, {A1:A;B1:B} <> ""}` – HaPhan Aug 07 '18 at 06:17
  • 1
    This solution does not seem to work / have the details necessary in the answer – zthomas.nc Jan 29 '20 at 22:11
  • yes, I realized I read the post incorrectly only after I posted (see my edit at the top). Still thought I'd keep it up though. – Quaris Jan 31 '20 at 01:40
1

In Row5:

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

Would need to be copied down however.

pnuts
  • 58,317
  • 11
  • 87
  • 139