Putting multiple filter criteria in one cell

**hanniversary** · 03-08-2021, 05:21 AM

Hi everyone hope you're all doing well !
I've been assigned a tourism product audit for my internship and I wanted to make it as best as I could. For that I made a spreadsheet listing town name and type of activity. I've been trying to find a way to filter cells that have multiple criteria. for example Moyvalley hotel and gold resort falls under the criteria of golf course and restaurant, as well as hotel and therefore i'd like to be able to filter these different words in the same cell so that when i choose just the golf course filter, Moyvalley hotel & golf course will show up. I have attached an image with a few examples of what i've done that might make more sense.

Anyone who can offer any help I would very much appreciate it ! Thank you so much for reading all this !

**Glenn Kennedy** · 03-08-2021, 07:57 AM

If you're just wanting to filter that list, on that sheet... filter on column B/text filters/contains/... and type in golf, or heritage, or whatever.

if you want a dropdown list on another sheet, then a formula is needed. So, what do you want?

**hanniversary** · 03-08-2021, 08:05 AM

Hi Glenn thank you for your reply. The spreadsheet will be used by my boss and our client so I would like to have a dropdown list to choose from to do this, if not I will have to explain to each person using the sheet to filter on column B/text filters/contains.. I can do this if its the only way but if there is another way without having to do filter/text filter/contains I'd be very interested !
Thanks again !

**Glenn Kennedy** · 03-08-2021, 09:14 AM

Hi. I set up a list of key words on sheet 3 (add to it as needed). I used this formula, to set up a dynamic named range (called "Key"... CTRL-F3 to view/edit):

=$A$2:INDEX($A:$A,AGGREGATE(14,6,MATCH({1E+100,"Zzzz"},$A:$A),1))

and set Key as the list for the dropdown box in sheet 2 B1. If you add more keywords to the list, they will immediately get added to the drop down list.

Then in sheet 2 A4, copied across and dnown:

=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$15)/(ISNUMBER(SEARCH($B$1,Sheet1!$B$2:$B$15))),ROWS(A$4:A4)))&"","")

Choose your keyword in B1 and it all autopopulates.

**hanniversary** · 03-08-2021, 09:59 AM

Wow that's really cool of you thanks so much dude !!!!

**Glenn Kennedy** · 03-08-2021, 10:07 AM

You're welcome.

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

Putting multiple filter criteria in one cell

LinkBack

Thread Tools

Rate This Thread

Display

Putting multiple filter criteria in one cell

Re: Putting multiple filter criteria in one cell

Re: Putting multiple filter criteria in one cell

Re: Putting multiple filter criteria in one cell

Re: Putting multiple filter criteria in one cell

Re: Putting multiple filter criteria in one cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Filter multiple sheets based on 3 criteria and return results into single cell

[SOLVED] Automatic filter by putting a criteria

[SOLVED] Putting OR inside a INDEX SMALL formula for multiple criteria, can it be done?

Filtering a Table and then putting text into a cell showing the current filter criteria

Multiple query criteria to filter cell value?

with variable document, filter by multiple criteria across multiple columns and copy cell

Multiple Filter Criteria in a Single Cell

Bookmarks

Bookmarks

Posting Permissions