Filter data by multiple columns (read prev posts but still struggling!)

[willisjules]

Hi everyone. I have what is probably a very simple quesion, but despite having spent the morning searching online and trying formulas I can't get anything to work.

I have a simple spreadsheet to track people booking onto events. Columns A to G cover personal data, then we capture everyone's 1st, 2nd, 3rd choice event in columns H, I and J. These are completed using a drop-down list of all events.

I'm trying to obtain a list of all the people wanting a specific event, so I need a formula to say whether H, I or J equals horseriding (for example). I need to return the full data where this is true as opposed to just counting the results.

Ideally I would like to then export the filtered list columns A to G to a new workbook. This is less important than the formula though because there's always cut n paste

Thanks for reading.

 

[Andrew Poulsom]

Welcome to the Board.

You can use Advanced Filter with OR criteria:

http://www.contextures.com/xladvfilter01.html#CriteriaRange

or you could use the formula:

=OR(H1="horseriding",I1="horseriding",J1="horseriding")

and AutoFilter that column for TRUE.

 

[willisjules]

Thanks. This is the page I was trying to follow before but I just can't get it to return but #VALUE?. I thought I was close with the OR criteria but then I read I need to filter that for true. No idea how to do that.

Basically this information is all aimed way over my head and I need more of an idiots guide because my brain hurts.

eta. I now see the formula which is very helpful. I think this is pretty much where I got to. I just don't understand how I AutoFilter for true.

 

[Andrew Poulsom]

For Advanced Filter, what are the headings for columns H, I and J?

For AutoFilter. you can use the formula I posted in a spare column next to your data (the column needs a heading so the formula would start in row 2:

=OR(H2="horseriding",I2="horseriding",J2="horseriding")

copied down.

Then select the column and choose Data|Filter|AutoFilter from the menu. Click the dropdown arrow and select TRUE.

 

[willisjules]

Oh it's working!

I obviously had the formula a bit wrong. It wasn't returning a true or false answer so I couldn't understand how to filter it.

You have made my day now. Very much appreciated!