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

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/fold...entry1194.aspx

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

--
Regards
Roger Govier

"jgraves" <(E-Mail Removed)> wrote in message
news:3F84F8CC-A4E9-44F1-8AB2-(E-Mail Removed)...
> 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, 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" wrote:

> 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/fold...entry1194.aspx
>
> You can type Acme* in the row above the filter, and all three different
> versions will show up.
>
> --
> Regards
> Roger Govier
>
> "jgraves" <(E-Mail Removed)> wrote in message
> news:3F84F8CC-A4E9-44F1-8AB2-(E-Mail Removed)...
> > 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
>
>

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.

--
Regards
Roger Govier

"jgraves" <(E-Mail Removed)> wrote in message
news:02D5D84A-729D-4154-9175-(E-Mail Removed)...
> 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" wrote:
>
>> 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/fold...entry1194.aspx
>>
>> You can type Acme* in the row above the filter, and all three different
>> versions will show up.
>>
>> --
>> Regards
>> Roger Govier
>>
>> "jgraves" <(E-Mail Removed)> wrote in message
>> news:3F84F8CC-A4E9-44F1-8AB2-(E-Mail Removed)...
>> > 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
>>
>>

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.

jgraves wrote:
>
> 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

--

Dave Peterson

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

"Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message
news:FBB0F976-AAB3-42B8-81D5-(E-Mail Removed)...
> 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.
>
> --
> Regards
> Roger Govier
>
> "jgraves" <(E-Mail Removed)> wrote in message
> news:02D5D84A-729D-4154-9175-(E-Mail Removed)...
>> 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" wrote:
>>
>>> 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/fold...entry1194.aspx
>>>
>>> You can type Acme* in the row above the filter, and all three different
>>> versions will show up.
>>>
>>> --
>>> Regards
>>> Roger Govier
>>>
>>> "jgraves" <(E-Mail Removed)> wrote in message
>>> news:3F84F8CC-A4E9-44F1-8AB2-(E-Mail Removed)...
>>> > 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
>>>
>>>

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

=exact(a2,upper(a2))



Fred Smith wrote:
>
> 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
>
> "Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message
> news:FBB0F976-AAB3-42B8-81D5-(E-Mail Removed)...
> > 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.
> >
> > --
> > Regards
> > Roger Govier
> >
> > "jgraves" <(E-Mail Removed)> wrote in message
> > news:02D5D84A-729D-4154-9175-(E-Mail Removed)...
> >> 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" wrote:
> >>
> >>> 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/fold...entry1194.aspx
> >>>
> >>> You can type Acme* in the row above the filter, and all three different
> >>> versions will show up.
> >>>
> >>> --
> >>> Regards
> >>> Roger Govier
> >>>
> >>> "jgraves" <(E-Mail Removed)> wrote in message
> >>> news:3F84F8CC-A4E9-44F1-8AB2-(E-Mail Removed)...
> >>> > 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
> >>>
> >>>

--

Dave Peterson

Thanks, Dave. Good catch. Fred.

"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I bet you meant to suggest the =exact() function:
>
> =exact(a2,upper(a2))
>
>
>
> Fred Smith wrote:
>>
>> 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
>>
>> "Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message
>> news:FBB0F976-AAB3-42B8-81D5-(E-Mail Removed)...
>> > 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.
>> >
>> > --
>> > Regards
>> > Roger Govier
>> >
>> > "jgraves" <(E-Mail Removed)> wrote in message
>> > news:02D5D84A-729D-4154-9175-(E-Mail Removed)...
>> >> 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" wrote:
>> >>
>> >>> 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/fold...entry1194.aspx
>> >>>
>> >>> You can type Acme* in the row above the filter, and all three
>> >>> different
>> >>> versions will show up.
>> >>>
>> >>> --
>> >>> Regards
>> >>> Roger Govier
>> >>>
>> >>> "jgraves" <(E-Mail Removed)> wrote in message
>> >>> news:3F84F8CC-A4E9-44F1-8AB2-(E-Mail Removed)...
>> >>> > 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
>> >>>
>> >>>
>
> --
>
> Dave Peterson