Case Sensitive Duplicates in Lists

[Dee Veloper]

A user is prompted for a 2 character code on a userform.
If the the code exists, the user is prompted for a differnent code.
The challenge is that AA is valid if Aa exists.

Using Exact and a Vlookup will not work given the vlookup returns the
first value it finds. this only works in the case where AA exists but
not aa. but when AA and aa are both in the list it no longer works.

thanks, ideally i'm looking for a vba solution.

Acceptable List
AA
Aa
aa
aA
BB

Unacceptable List
AA
AA
AA

[Jason Morin]

Posting your code or formula always helps readers
understand your problem better and will usually expedite a
response. With a formula, you could use:

=SUMPRODUCT(--(EXACT(J1,A1:A10)*ROW(INDIRECT("1:"&ROWS
(A1:A10)))))>0

This will compare the value in J1 against the range A1:A10
and return a TRUE if the value already exists in the
range, FALSE if not.

It *is* case-sensitive.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>A user is prompted for a 2 character code on a userform.
>If the the code exists, the user is prompted for a
differnent code.
>The challenge is that AA is valid if Aa exists.
>
>Using Exact and a Vlookup will not work given the vlookup
returns the
>first value it finds. this only works in the case where
AA exists but
>not aa. but when AA and aa are both in the list it no
longer works.
>
>thanks, ideally i'm looking for a vba solution.
>
>Acceptable List
>AA
>Aa
>aa
>aA
>BB
>
>Unacceptable List
>AA
>AA
>AA
>.
>