I have this formula:
=QUERY(
{AG4:AW101},
"Select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17
where Col1 is not null
and Col1 is not null
and Col2 is not null
and Col3 is not null
and Col4 is not null
and Col5 is not null
and Col6 is not null
and Col7 is not null
and Col8 is not null
and Col9 is not null
and Col10 is not null
and Col11 is not null
and Col12 is not null
and Col13 is not null
and Col14 is not null
and Col15 is not null
and Col16 is not null
and Col17 is not null
order by Col"&$N$1&" "&$N$2&"")
Is there anyway to make it shorter? Do I really need to repeat and ColX is not null
for every column?
CodePudding user response:
join them. lets say your range is A:F and you want to check B:F for is not null
, then try:
=ARRAYFORMULA(QUERY({A:F, TRIM(FLATTEN(QUERY(TRANSPOSE(B:F),,9^9)))},
"select Col1,Col2,Col3,Col4,Col5,Col6
where Col7 is not null", ))