CODEWITHSUNDEEP
google-sheets
lance panda
lance panda

Reputation: 5

Only odd columns in arrayformula on google sheets

I have multiple columns that I need to stack into one column. But I only want to have odd columns in the list.

This is the formula I'm working with, which works to get all in one column. But not just odd ones =TRANSPOSE(SPLIT(ARRAYFORMULA(CONCATENATE(TRANSPOSE(FILTER(A:D,A:A>0)&""))),""))

https://docs.google.com/spreadsheets/d/1yAPw13VE7uclP0ILzUYxZBgLGid9PrZXpWmhuEgi1S0/edit?usp=sharing

Appreciate the help.

Upvotes: 0

Views: 1605

Answers (2)

Krzysztof Dołęgowski
Krzysztof Dołęgowski

Reputation: 2660

You can also use QUERY with 'skipping 2' option. This takes only every 2nd row so to make it useful you have to transpose your range first. Then you FLATTEN your columns. As you can see, there's a different order of values:

=flatten(query(transpose(A1:D4),"select * skipping 2 "))

enter image description here

Upvotes: 2

Erik Tyler
Erik Tyler

Reputation: 9345

This should do it:

=QUERY(FLATTEN(FILTER(A:D,ISODD(COLUMN(A:D)))),"Select * Where Col1 Is Not Null")

FILTER filters in all cells from odd columns.

FLATTEN makes one column of all of the FILTERed results.

QUERY weeds out blank rows.

Upvotes: 1

Related Questions