Can I Make AutoFilter to be Case-Sensitive?

[jgraves]

I am using Excel 2003 SP3.
I have a column of text that contains several unique values:

AcmeCompany
Acme Company
ACME COMPANY

I want the user to see how many versions of their company name they have in
this column. I want to see all these entries in the auto-filter drop down,
but I only see the first two because of their spelling. Is there any way I
can accomplish this using autofilter? Any other ideas for displaying this
information to a user in a simple way?
I want to actually select the entire record that uses a particular spelling,
too, otherwise I would use Advanced Filter.
Thanks.
Jen G

 

[Roger Govier]

Hi

Take a look at the FastFilter utility that I wrote, which is available at
http://www.contextures.com/Fastfilter.zip
or
http://excelusergroup.org/files/folders/allversionwb/entry1194.aspx

You can type Acme* in the row above the filter, and all three different
versions will show up.

 

[jgraves]

Roger, this is a truly nifty utility! I can picture other uses for it, but
this situation doesn't quite fit. I need to be able to see each unique value
in the auto-filter drop down. Your utility will not show me this, unless I am
missing something in your instructions.
Thanks,
Jen

 

[Roger Govier]

Sorry Jen

Misread your question.
There is no way that you can get the Autofilter dropdown to distinguish
between cases.
The lack of a space, is different, and that will show as one entry, but the
others, be they in lower or uppercase will only show once.

 

[Dave Peterson]

Could you use a helper column with a formula in it?

If yes, you could put:
=a2<>substitute(a2,"ACME","")

If you see true, then A2 contains the uppercase ACME characters.

=substitute() is case sensitive, so if A2 contains ACME (all uppercase), then
=substitute(a2,"ACME","") will be different than the original string in A2.

If ACME isn't in A2, then the =substitute() won't change the original string.

Then drag it down the column and filter by true/false.

 

[Fred Smith]

You can distinguish between upper and non-upper case by using a helper
column with the formula:

=a2=upper(a2)

This will give you True if a2 is in all upper case, and false if not. Now
you can filter for it.

Regards.
Fred

 

[Dave Peterson]

I bet you meant to suggest the =exact() function:

=exact(a2,upper(a2))

 

[Fred Smith]

Thanks, Dave. Good catch. Fred.

I bet you meant to suggest the =exact() function:

=exact(a2,upper(a2))