Search code examples
google-sheetsarray-formulas

How not to return empty cell with ArrayFormula and condition to ignore

I have a sheet with a pivot table. I want to return it by line to import it elsewhere. I created an array formula for this.

Here is my formula :

=ArrayFormula(SPLIT(flatten(PROPER('Doc'!A1)&"🦊"&'Doc'!G19:G29&"🦊"&'Doc'!E19:E29&"🦊"&'Doc'!F19:F29&"🦊"&'Doc'!J18:O18&"🦊"&'Doc'!J19:O29);"🦊"))

I don't want to return a line if a cell within 'Doc'!J19:O29 is empty to avoid having to much line in the generated doc.

Also I would like to ignore some line from 'Doc'!G19:G29 when there is the word "avoid". I could not find a way to do it.

I tried adding a IF condition but did not work.

=ArrayFormula(if('Doc'!G19:G29="Avoid";;SPLIT(flatten(PROPER('Doc'!A1)&"🦊"&'Doc'!G19:G29&"🦊"&'Doc'!E19:E29&"🦊"&'Doc'!F19:F29&"🦊"&'Doc'!J18:O18&"🦊"&'Doc'!J19:O29);"🦊")))

Here is the exemple : enter image description here

Thank you

Solution

  • Try:

    =ArrayFormula(QUERY(BYROW(SPLIT(flatten(PROPER('Doc'!A1)&"🦊"&'Doc'!G19:G29&"🦊"&'Doc'!E19:E29&"🦊"&'Doc'!F19:F29&"🦊"&'Doc'!J18:O18&"🦊"&'Doc'!J19:O29);"🦊"),LAMBDA(r;IF(OR(COUNTBLANK(r);COUNTIF("Avoid";r));;r))),"where Col1 is not null"))