CODEWITHSUNDEEP
google-sheetsarray-formulasgoogle-sheets-query
Eli
Eli

Reputation: 91

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

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

Upvotes: 5

Views: 10690

Answers (4)

Gabriel S
Gabriel S

Reputation: 41

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().

Upvotes: 4

Quaris
Quaris

Reputation: 359

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.

Upvotes: 2

ttarchala
ttarchala

Reputation: 4577

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.

Upvotes: 3

pnuts
pnuts

Reputation: 59495

In Row5:

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

Would need to be copied down however.

Upvotes: 1

Related Questions