google-sheetsfiltergoogle-sheets-formulais-emptydynamic-columns

Query/filter out blank non-specified columns from range

I would like this multiple-criteria query not to show empty columns.

Query

=QUERY({H3:M11}, "select * WHERE
"&TEXTJOIN(" and ", 1, 
 IF(C3<>"", "Col2 = "&C3&"", ), 
 IF(B3<>"", "Col3 = '"&B3&"'", )), 1)

Besides, I would also like to know if it's possible to filter it outside a query formula. Currently, I have this formula made by @player0 which is excluding columns with values greater than 0, but I didn't manage to make it work for text.

Filter

=FILTER(FILTER(H3:M11, LEN(TRIM(QUERY(IFERROR(1/(1/H4:M11)),,9^9)))>0), {9; 
  LEN(TRIM(FLATTEN(QUERY(TRANSPOSE(IFERROR(1/(1/H4:M11))),,9^9))))}>0)

Link to the question where this filter formula was found.

Here's the sheet.

Thanks a lot.

Solution

  • try:

    =ARRAYFORMULA(QUERY({H3:K11, 
 FILTER(L3:M11, TRIM(QUERY(L4:M11,,9^9))<>"")}, 
 "where "&TEXTJOIN(" and ", 1, 
 IF(C3<>"", "Col2 = "&C3&"", ), 
 IF(B3<>"", "Col3 = '"&B3&"'", )), 1))

    enter image description here