Filtering a list of values

[ccampb18]

This seems really basic but I cannot find an easy way to do this. I have a column that contains values,lets say A-M. I am wanting to filter based on a list of values, like, A, C, D, K, M. I thought I was certain that I have seen something that says "In list" which helped you filter a list of values. Any ideas? Thanks for your help.

 

[Colin Legg]

Hi,

Which version of Excel are you using?

 

[ccampb18]

2003

 

[Colin Legg]

Sticking with an autofilter, you could add a helper column to your table to act as a TRUE/FALSE indicator which you can filter by. So, for example, if the letters are in column D, the formula in your helper column could be:

=OR(D2={"A","C","D","E","M"})

 

[ccampb18]

I could not get that to work. I put that formula in a separate column with the values I am needing and I kept getting an error message.

 

[Colin Legg]

It 'works' for me. What exact formula did you try?

 

[ccampb18]

In cell W2, I put
=or(i2=("0663", "1325", "3100", "3510", "3154", "3511", "3512", "3505")

I tried it with and without the quotations since these aren't necessarily numerical values. I think they are formatted as a text.

 

[Colin Legg]

Ah okay... you're using the wrong type of brackets.We need to use the curly brackets { } to symbolise an array constant.

Try copying this into your worksheet:

=OR(I2={"0663","1325","3100","3510","3154","3511","3512","3505"})

The numerical equivalent would be:

=OR(I2={663,1325,3100,3510,3154,3511,3512,3505})

Hope that helps...

 

[ccampb18]

Ha duh! Thanks, that worked great!