CODEWITHSUNDEEP
google-sheets
vanscurvy
vanscurvy

Reputation: 33

Dynamic number of WHERE conditions in Google Sheet query function

I'm building a spreadsheet that can be used to import and then filter wireshark capture .csv's. The idea is - you import the .csv into the second tab, and on the first tab you are able to display the data based on a number of include/exclude filters. Here is a dummy sheet for reference:

https://docs.google.com/spreadsheets/d/17TtZHRiXCaH1XClq5SGRdIuyLZnMOrl6Ygrbfo9BbfM/edit?usp=sharing

As you can see in the spreadsheet, I have four possible "include" values and four possible "exclude" values, each being determined by selection of a drop down-list. The intention is for the QUERY() function in cell A6 to display the data based on these filters.
Example of the filter at work

I know how to use WHERE to do it for a single filter condition:

=query('Import CSV Here'!1:349,"select * where ("&B4&"="&B2&")")

And how to do it for multiple filter conditions:

=query('Import CSV Here'!1:349,"select * where ("&B4&"="&B2&") and ("&C4&"="&C2&")" )

What I can't figure out is how to write the QUERY function so that it only factors in a WHERE condition for filter columns that aren't blank (and therefore have been actively populated from their drop down menus). Is there an efficient way to do this?

Upvotes: 2

Views: 8141

Answers (1)

Robin Gertenbach
Robin Gertenbach

Reputation: 10806

You can wrap each filter condition in an if clause and use put something that evaluates to true if it is blank.

=query(
  'Import CSV Here'!1:349,
  "select * WHERE " &
     IF(ISBLANK(B2), "1=1", B4 & "=" & B2) &
     " and " & IF(ISBLANK(C2), "1=1", C4 & "=" & C2) )

Upvotes: 1

Related Questions