CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
bibihub
bibihub

Reputation: 3

Google sheet - Unpivot table and skip blank cells

I cannot work this out:

In Google sheet, I'm trying to unpivot a wide and long table by using this formula:

=ARRAYFORMULA((SPLIT(FLATTEN(sheet1!$J$3:$J&"//"&sheet1!$O$2:$CA$2&"//"&sheet1!$O$3:$CA);"//")))

However, this table is full of empty cells and I'd like to skip them during the process because the output of the formula in sheet2 columns A B and C is too long for nothing.

So far, I've only managed to process it again using:

=QUERY('sheet2'!A1:C;"SELECT A,B,C WHERE C >0";0)

Which works great. However I need to do it in one step because my future set of data will be longer, so I'll probably reach the far end of the sheet (and I'd prefer not having an intermediary table).

Can anyone work this out?

Upvotes: 0

Views: 874

Answers (2)

Aresvik
Aresvik

Reputation: 4630

Try this on sheet2:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(sheet1!$J$3:$J&"//"&sheet1!$O$2:$CA$2&"//"&sheet1!$O$3:$CA);"//");"where Col3 is not null";0))

Upvotes: 1

MattKing
MattKing

Reputation: 7783

you just put the second part of what you did around the first:

=QUERY(ARRAYFORMULA((SPLIT(FLATTEN(sheet1!$J$3:$J&"//"&sheet1!$O$2:$CA$2&"//"&sheet1!$O$3:$CA);"//")));" WHERE Col3 >0";0)

Upvotes: 1

Related Questions