CODEWITHSUNDEEP
google-sheets
Bengya
Bengya

Reputation: 115

Google Sheets - Embedded arrays with multiple columns under each other

I'd like to insert 2 column wide fields under each other. I tried with embedded arrays but was not successful. So basically from:

a    1     e    5
b    2     f    6
c    3

I would like to get:

a    1
b    2
c    3
e    5
f    6

I tried with ={{A:A,B:B};{C:C,D:D}} but could not get it working, however ={{A:A,B:B},{C:C,D:D}} put the columns the same as they were so its intresting that with ; its not working. The blocks are always 2 column wide but the rows are different length

Thanks for your help in advance!

Upvotes: 0

Views: 710

Answers (3)

KatieBev
KatieBev

Reputation: 46

Try:

=filter({A:B;C:D},{A:A;C:C}<>"")

This will return rows where Columns A or C are not blank.

Upvotes: 1

Elaskanator
Elaskanator

Reputation: 1216

You're not going to find a clean built-in formulaic solution to this one that doesn't utilize some sort of built-in magic auto expansion (like pnuts's answer). Here is my approach using OFFSET that will also work in Microsoft Excel.

In two columns, copy this formula.

=OFFSET($A$1,(ROW()-ROW($G$1))/2,IF(MOD(ROW()-ROW($G$1),2)=1,2,0))

In the second column, modify the formula, adding 1 to the column offset parameter:

=OFFSET($A$1,(ROW()-ROW($G$1))/2,1+IF(MOD(ROW()-ROW($G$1),2)=1,2,0))

where $A$1 is replaced with the address of the top left of your range and $G$1 is the starting location of your output range. This should be resistant to auto-update of formulas from range insertions and deletions (which I despise butchering my formulae and conditional formatting rules) by using only the bare number of references, which are all absolute.

This works by dividing the row offset from your starting position by 2 and rounding down (via an implicit cast to integer when used as a parameter to the OFFSET function) to get the row number of your input range. Then it shifts over 2 columns on every odd row to get data from the second column pair.

Note this is not a size-aware function, so it interweaves the second column pair:

a    1
e    5
b    2
f    6
c    3

Upvotes: 0

pnuts
pnuts

Reputation: 59440

Just under c assuming a is in A1:

=ArrayFormula(C1:D2)

Upvotes: 0

Related Questions