Filtering Range based on Multiple Criteria

Question

I am trying to filter a list of properties based on multiple keywords (e.g. "Cool Interior," "Terrace/Patio"). Here's a basic interpretation:

The range I want to filter is on a sheet titled, "1," and spans from A2:O25. The range is a Protected Range, called "Properties_List." Each row represents a different property, where each column represents different information (Name, Region, Price, Size, etc.). In Column AA of sheet "1," I have a list of keywords for each property that are separated by commas. A sample cell in Column AA looks like this: Mountain, Lake, Terrace/Patio, Multi-Unit, Garden. The range in Column AA is a Protected Range, called "Filter_List," and spans from AA2:AA25.

[Note: Each row in "Filter_List" uses a "JOIN" function, which strings multiple selected keywords from Columns Q-Y. Each cell in Columns Q-Y contains dropdown boxes, where you can select one term out of 25-30 keywords.]

I have added a Code.gs script for a separate sheet, sheet "2." The same dropbox (as mentioned above) sits in cell A4 of sheet "2." From the script, the user can select a keyword from the dropbox in cell A4, and that selection will appear in cell B4. Additionally, the user can select more keywords, and the selections will appear (separated by commas) after the previous selection(s) in cell B4. After three separate selections, cell B4 would like this: Mountain, Garden, Lake

What I am struggling to do is this:

Filter "Properties_List" in cell A7 of sheet "2," based on the selections in cell B4 that match the text strings in the "Filter_List."

So, any property that contains the words "Mountain" AND "Garden" AND "Lake" within its corresponding cell in the "Filter_List," will show up in A7 (and down) on sheet "2."

In my attempt, I have also designated cells C36:H36 in sheet "2" to "grab" each selection in cell B4. So in our example above, cell C36 would show "Mountain," cell D36 "Garden," cell E36 "Lake" and cells F36:H36 would be blank.

I have used the following function in cell A7 of sheet "2":

=FILTER(Properties_List,REGEXMATCH(Filter_List,C36),REGEXMATCH(Filter_List,D36),REGEXMATCH(Filter_List,E36),REGEXMATCH(Filter_List,F36),REGEXMATCH(Filter_List,G36),REGEXMATCH(Filter_List,H36))

This kind of works? But not fully. What I have found is the function will also filter based on the order of the selections. In other words, only properties with a "Filter_List" string where "Mountain" came before "Garden" which came before "Lake" would remain. I want the filter to show a property where "Mountain," "Garden" and "Lake" (all three) are contained ANYWHERE in the string.

Does this make sense? At all? Haha. Thanks for any help, and please let me know if I am going about this all the wrong way!

please share a sample sheet with example of your data and desired result. You can Make an anonymous sample document — z..
– z.., Commented Jan 19, 2024 at 20:46

Tedinoz · Accepted Answer · 2024-01-20 05:40:19Z

0

Try this formula in Cell A5 of the filter sheet:

=query({Sheet1!A1:C},"select Col1, Col2, Col3 where Col1 is not null "&"and Col3 contains '"&textjoin(" and Col3 contains ",1,split(regexreplace(B2,"(\w+)","'$1'"),",")))

SAMPLE DATA

SAMPLE REPORT

LOGIC

regexreplace(B2,"(\w+)","'$1'"): places single quotes around each word in the comma separated list. h/t @TomSharpe : Is there a way to wrap a list of comma separated values from a cell in quotation marks?
split: removes each comma and creates a range
textjoin: enables a where clause when the number of attributes is indeterminate. h/t: @player0: Google Sheets Query sum indeterminate number of columns

answered Jan 20, 2024 at 5:40

Tedinoz

8,3943 gold badges30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Filtering Range based on Multiple Criteria

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related