How do i advanced filter rows based on specific data that could in a large range of cells?

[ao747]

Hi all,

I'm fairly new to Excel and I've been playing with the advanced filter function and have been trying to understand how I can filter rows based on a specific piece of data being in one of a large range of cells.

For example, if I list a group of people's 10 favorite restaurants (one person and their favorites in each row), how do i filter rows based on the people who mentioned that restaurant in any one of the 10 columns listing their favorite restaurants? If I have data in 300 columns then how do i filter for people who mentioned a specific restaurant in one of those 300 columns.

I attached a small sample of data - in this example, how would i copy/filter only the people who listed "Tillie's" somewhere in their responses?

Cheers!
Andy

[jeffreybrown]

Hi Andy & Welcome to the Forum,

Here is one way. In L1 I put the search term.

In a helper column, Column L >> L3 and down >> =IF(COUNTIF($H3:$K3,"*" & $L$1 & "*"),"Yes","No")

Now filter on "Yes" from column L

[ao747]

Thanks Jeff, this is really useful!

[jeffreybrown]

Here is the macro to move the data to it's own sheet.

Please Login or Register  to view this content.

[ao747]

Thanks! Is it possible for the macro to create a new worksheet each time for each filter (so create 12 worksheets with each individual filtered data set on).
Thanks for your help with this I spent 10 hours trying to work this out over Christmas!

[jeffreybrown]

Is it possible for the macro to create a new worksheet each time for each filter (so create 12 worksheets with each individual filtered data set on).

Certainly, but I'm stepping out right now to spend some time with the grandchildren. I take a look upon my return.

[jeffreybrown]

Hi Andy,

See how this works out for you. Just change the value in N1 and then press the button.

We could make this a Worksheet change event off of N1, but would have to make some VBA edits to check for the return of any records and cancel if no records.

Something to think about.

Please Login or Register  to view this content.

[jindon]

If you prefer AdvancedFilter then

Please Login or Register  to view this content.

As you change Sheet1!A2, it runs and no need to care about the number of columns.

[ao747]

Jeff this is fantastic, thank you! It gives me exactly the data that I need. I really appreciate the help, following along in the spreadsheet also helped me see where I was going wrong.

Thanks again!
Andy