using advanced filter to find unique values 1

[lucrahouse]

I was trying to use the advanced filter under data/filter and my criteria was just one character such as the letter "T" but unfortunately it also gave other values which began with the letter "T" such as "TGT" or "TRP".

I double checked and made sure I was filtering for only unique values, but it didn't help.

Do you have any suggestions?

 

[Zathras]

That's an interesting "feature" of criteria ranges of which I was not previously aware.

There may be a straight-forward way to tell Excel to use exact matching, but until someone who knows can tell us, here is a work-around:

Use a 2-column criteria range:

Assuming the data are set up this way:

A1: 'STUFF
A2: 'AT
A3: 'Tigers
A4: 'Tripoli
A5: 'T
A6: 'T
A7: 'Tango

Then you can set up a criteria range like this:
B1: 'STUFF
B2: 'T
C1:
C2: =LEN(A2)=1

Note that C1 remains blank when C2 has a formula.

 

[xlbo]

This is beacuse excel is trying to be clever - it boils down to how it interprets filter criteria.
Instead of just entering T, enter:
="=T"

so that in the cell, you can see =T (if you just write =T in the cell, it will think you are trying to refer to a named range)

Should work fine if you do it like that

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi lucrahouse,

Geoff is correct; according to Help ..

[ul]When you use text as criteria with an advanced filter, Microsoft Excel finds all items that begin with that text. For example, if you type the text Dav as a criterion, Microsoft Excel finds "Davolio," "David," and "Davis." To match only the specified text, type the following formula, where text is the text you want to find.

=''=text''[/ul]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[lucrahouse]

Thank you for your help, the "=T" worked.