0

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.

iamthe202
  • 39
  • 6
  • Make sure to add input and expected output as **text table** (NOT as IMAGE/LINK) to the question. [Click here](https://webapps.stackexchange.com/a/161855/) to create a table easily. Adding such tables greatly increases your chances of getting a elegant answer, as **it is easier to copy/paste**. If you share spreadsheets, your question maybe closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). Your table should be a [mre].[Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. – TheMaster Oct 23 '22 at 16:59
  • It looks like you copied this from somewhere else. If you're using someone else's work without giving them credit, that constitutes plagiarism, which is not welcome on Stack Exchange. To fix it, you can [edit], include a [link](/editing-help#links) to the source, mention the author's name, and [quote](/editing-help#simple-blockquotes) the copied content. For more details, see [referencing help](/help/referencing) and [this FAQ](https://meta.stackexchange.com/q/160077/343832). – TheMaster Oct 23 '22 at 17:04
  • Thanks. I just added the code. As for the image, it's to illustrate my desired result. As for the external link, it's to make it easier to copy my sheet and test it. – iamthe202 Oct 23 '22 at 17:07
  • I did mention the author of the code. But I will find and add links to the source. – iamthe202 Oct 23 '22 at 17:09

1 Answers1

1

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • Works great. How do I include zeros besides blanks? Example: Don't show anything <>"" or <>0. – iamthe202 Oct 23 '22 at 20:20
  • @iamthe202 try: `=ARRAYFORMULA(QUERY({H3:K11, FILTER(L3:M11, TRIM(QUERY(REGEXREPLACE(""&L4:M11, "^0$", ),,9^9))<>"")}, "where "&TEXTJOIN(" and ", 1, IF(C3<>"", "Col2 = "&C3&"", ), IF(B3<>"", "Col3 = '"&B3&"'", )), 1))` – player0 Oct 23 '22 at 20:24
  • 1
    Perfect, as always. Thank you. – iamthe202 Oct 23 '22 at 21:11