does not contain letter filter

M

Memphus01

I am trying to do an advanced filter using data in the rows above my data

i have four columns that I am filtering.

columns A-C contain numeric data and I am not having any issue with those
filters.

column D has a three letter rating system ranging from AAA to FFF (each of
the three letters could be A,B,C,D,F...for example you could have ACA, ABD,
ADD, etc...)

I would like to filter that column for records that does not contain a C,D,
or F.

I tried ="<>*C*" (for example) but it is not working.

how can I write that formula so that it looks at all three letters and
excludes any C's,D's,F's?

Thanks
 
D

Debra Dalgleish

In the criteria range, put the rating column heading in the top 3 cells.
In the 3 cells below, enter criteria to exclude each of the 3 letters.

The criteria range will look like this:

Rating Rating Rating
<>*C* <>*D* <>*F*
 
S

Shane Devenshire

Debra's answer is correct.

If the result you are getting is not correct then you are 1. not following
the instructions properly or 2. you are not telling us everything.

Show us a column of data, show us you criteria range, and show us the
results you are getting and the results you want.

1. Suppose the title is in cell D1 and is Code. Then in F1:H1 enter the
word Code in each cell. In F2:H2 enter
<>*c* <>*d* <>*F*
2. Select the range D1:D100 anc choose Data, Filter, Advanced Filter
3. Make sure the List Range is D1:D1000
4. Click in the Criteria Range box and pick F1:H2
5. Click OK.
 
D

Debra Dalgleish

What headings do you have on the Data columns?
You should have the same headins in the criteria range.

Also, all 3 of the letter criteria need to be on the same row, not in
separate rows.

When they're in the same row, the Operator is And, e.g. doen't contain C
AND doesn't contain D AND doesn't contain F

If you have them on separate row, the Operator is OR, which will let
unwanted characters slip through the filter.

Add your headings in the first row of the heading, and three rows of
criteria below, e.g.

Col01 Col02 Col03 Code Code Code
 
M

Memphus01

Thanks Debra- I can't believe I didn't think about the fact that each filter
was letting the other letters in!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top