Multiple Column Filter

[kcaitch]

I have four columns that are, for example, favorite food 1, fav 2, fav 3, and fav 4. In each of these columns, I want to select from a drop down a type of food. The available types of foods are the same in each column. After I enter the favorite foods of say ten different people, I want to be able to filter all and just see the people that like popcorn, chips, and taffy, regardless of which favorite column it shows up.

My assumption is that this complex filter could not (easily) be achieved through the auto filter or advanced filter tools, so I think it is a VBA or macro solution. I don't have much experience at all in writing this sort of thing, so I thought I would see what everyone on here thinks.

Any ideas?

Thanks,
Ken

Screen Shot.jpg

[MarvinP]

Hi Ken and welcome to the forum,

This is a great place for Excel's Advanced Filter. You would use an "OR" criteria by putting the same food on separate rows. Read about advanced filters at:
http://www.contextures.com/xladvfilter01.html

Look down for the section called And vs OR to see what I mean. (Note - column head words must be different for each column)

[ajryan88]

Hi,

Take a look at the attachment. The blue section contains the formula to extract the people from drop-down lists above. This is a dynamic table and will update automatically if you change someone's favourite food (or just press F9 to recalculate the worksheet, I put a formula into the food selection section to randomise the foods for testing purposes). Is this the sort of thing you were after? Even if you didn't want it in a table form like this, this style can be adapted to display a single food only, where you can select that food from a drop-down box also.

Let me know. I hope this helps