Filter a range created dynamically via QUERY in Google Sheets

Question

I'm using this query currently

=sort({IFERROR(query('PI Calcs'!C4:K51,"select C,G,H, K where C is not null and G > 0"),{"","","",""});IFERROR(query('PI Calcs'!C54:P101,"select C,J,K,P where C is not null and J > 0"),{"","","",""});IFERROR(query('PI Calcs'!C104:P127,"select C,J,K,P where C is not null and J > 0"),{"","","",""})},2,false)

I'm joining 3 ranges together, through the use of QUERY. I'm using IFERROR because if any of the queries return an empty set then I cannot merge them together, the fail result is an empty row of 4 values.

My difficulty is removing those empty values from the final set, as when sorting them I get 1 empty row for each failed query. I've tried using FILTER but I have no way to refer to the column in the range as it's dynamically created.

How can I filter the blank rows out of a dynamically created range? Is there a way to query and join the results without falling back on an empty range?

player0 player0 · Accepted Answer · 2020-02-13T21:12:11

try this shorter one:

=QUERY(SORT({IFERROR(QUERY('PI Calcs'!C4:K51, 
 "select C,G,H,K 
  where C is not null 
    and G > 0"), {"","","",""});
 IFERROR(QUERY({'PI Calcs'!C54:P101; 'PI Calcs'!C104:P127},
 "select Col1,Col8,Col9,Col14 
  where Col1 is not null 
    and Col8 > 0"), {"","","",""}), 2, 0), 
 "where Col1 is not null", 0)

or even shorter:

=QUERY({'PI Calcs'!C54:P101; 'PI Calcs'!C104:P127; 
 {'PI Calcs'!C4:I51, 'PI Calcs'!G4:L51,'PI Calcs'!K4:K51}},
 "select Col1,Col8,Col9,Col14 
  where Col1 is not null 
    and Col8 > 0
  order by Col2 desc", 0)

Filter a range created dynamically via QUERY in Google Sheets

2 Answers