10

I would like to perform a multi criteria search of data in a column- contains data of check boxes(more than one option chosen).

For a clearer picture of what I am trying to do, screenshot below is a question in a form

enter image description here

Data from the form are saved in sheets like below,

enter image description here

So my concern here is if I would like to search/filter for the rows that contain "Commercial", the rows with Commercial,Engineering doesn't show up. That's definitely not an effective search.

Any advise on how can I go about this issue is kindly appreciated. If

lily
  • 385
  • 1
  • 3
  • 21

3 Answers3

15

Let's say you have your form in the response sheet in columns A to P, with the multiple choice in col D. If you want to filter your data on the word 'Commercial' you can either do:

=filter(A2:P, regexmatch(A2:P, "Commercial"))

or use query():

=query(A2:P, "select * where B contains 'Commercial' ")

Note: depending on your locale you may have to change the commas to semi-colons in order for the formulas to work.

I hope that helps ?

JPV
  • 26,499
  • 4
  • 33
  • 48
  • For instance I have 16 options/check boxes for each question, do I create filter views for each or how else is the work around? – lily Mar 10 '15 at 08:38
  • That depends on what you want to achieve. Do you want to be able to run multiple filters at once ? Else, you can have a dropdown box on the sheet where you want the filter (say in cell B1), will all these 16 options. Then you can change the filter to =filter(A2:P, regexmatch(A2:P, B1)) or the query to =query(A2:P, "select * where B contains '"&B1&"' "). Now every time you change the value in B1, you will have the data filtered in a different way.. – JPV Mar 10 '15 at 08:48
  • What would the workaround be to run multiple filters at once? – lily Mar 10 '15 at 09:01
  • 1
    Assuming you have the words you want to filter by in range E2:E6 you could try: =filter(B2:B, regexmatch(B2:B, JOIN("|", E2:E6))) – JPV Mar 10 '15 at 09:09
  • for speed's sake... `filter` is so much faster than `query`. You won't see the speed difference in one function, but if you have a page of them, you definitely want to avoid a page of `query`. – jason Dec 29 '18 at 02:26
3

Following JPV's answer, I developed a line to make the query useful if you want to cross two categories. Let's suppose that someone in your checkbox example had picked all the options (IT, HR, Commercial, Engineering); and that you have created the cell with the dropdown option box in cell B1 with all your options, as JPV said.

Then, you want to filter and see all the people who had chosen IT and Commercial. You would, for that, create a second cell with the dropdown option box in, lets say C1; and then your query would be:

=query(A2:P, "select * where B contains '"&B1&"' and B contains '"&C1&"' ")
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
1
=FILTER(MOBILE!A2:E2000, ISNUMBER(SEARCH(A1,MOBILE!A2:A2000)))

SEARCH function will return a number of the position of the searched word (A1) in the searched strings in range (MOBILE!A2:A2000).

If the result of search is a number (ISNUMBER), then filter will return the TRUE rows result from the range MOBILE!A2:E2000.

Jamie Twells
  • 1,924
  • 4
  • 26
  • 56
Ori O
  • 11
  • 1