Data Validation, Identify Duplicates and Limit imput

[Lilyput]

I have a sheet where I have a reference number in Col D that is comprised of
3 letters, a space then 3, 4, 5 or 6 digits.. I need to identify duplicate
entries as people enter a duplicate reference number

Col D
ABC 1234
ABS 22221
ABR 124
ASR 554477

I have managed to find a formula within data validation that will give an
error message if a duplicate reference is entered however I need to add
something to make sure that only three letters , then a space, then numbers
can be input or that peoiple can only input letters and numbers with no
spaces or other characters.

The formula in my data validation is =COUNTIF(D$2$4999,D2)<=1

Any suggestions very welcome - most entries are being input correctly
however a few are including a / between text and numbers instead of space or
are adding a space after numbers so my current formula is not picking up
these as duplicates.

I'm using Excel 2000

Lilyput

 

[Ashish Mathur]

Hi,

Try to use the following formula in Data > Validation > Custom

=AND(CODE(LEFT(C13,1))>=65,CODE(LEFT(C13,1))<=90,CODE(MID(C13,2,1))>=65,CODE(MID(C13,2,1))<=90,CODE(MID(C13,3,1))>=65,CODE(MID(C13,3,1))<=90,CODE(MID(C13,4,1))=32,ISNUMBER(1*(RIGHT(C13,LEN(C13)-SEARCH("
",C13)))))

This will allow a user entry where the first three characters are
capitalised alphabets, fourth character is a space and after the space there
is a number

Hope this helps

 

[T. Valko]

That formula allows entries like:

ABC 1E100
ABC 1.5
ABC 1

 

[Ashish Mathur]

Hi,

Thank you for pointing this out. This modification takes care of problem 1
and 2. Problem 3 still persists - infact if I put a test for the numeric
portion being between 3 and 6 digits, the formula becomes long enough not to
be accepted in Data > Validation > Custom

=AND(CODE(LEFT(C13,1))>=65,CODE(LEFT(C13,1))<=90,CODE(MID(C13,2,1))>=65,CODE(MID(C13,2,1))<=90,CODE(MID(C13,3,1))>=65,CODE(MID(C13,3,1))<=90,CODE(MID(C13,4,1))=32,mod(1*(RIGHT(C13,LEN(C13)-SEARCH("
",C13))),1)=0)

 

[T. Valko]

See the formula I suggested to the OP. There's also a link to a sample file
for demonstration.

The formula is "ugly" even by my standards!

I don't know how to limit the length of the number portion since it varies
from 3 digits to 6 digits so about the only thing I could think of was to
test for an allowable min and max string length.

ABC 123 = 7 characters
ABC 1234 = 8 characters
ABC 12345 = 9 characters
ABC 123456 = 10 characters

So the entry must be at least 7 characters but not more than 10 characters.

The OP seems to be having trouble implementing this in their application,
though.

 

[Lilyput]

Thanks very much indeed both of you - I have managed to get my sheet to stop
invalid entries and show duplicates. I've used Ashish' last formula in data
validation and added a column with if formula to show duplicate entries. Biff
I just could not get your formula to work in data validation no matter how I
enter it - when I highlighted the whole range to enter the data validation it
chnaged D2 to D64529 in all cells so not a clue what is going on!

Anyway I have managed to get my workbook to do what i want so thanks!

P.S. - sorry for delay responding to your assistance but been away all week!

 

[T. Valko]

Good deal. Thanks for the feedback!

 

[Ashish Mathur]

You are welcome