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

 

[T. Valko]

3 letters, a space then 3, 4, 5 or 6 digits

ABC 1234
ABS 22221
ABR 124
ASR 554477

Do the letters have to be in UPPERCASE? Is this allowed:

abc 1234
aBc 1234
Abc 1234

 

[Lilyput]

The letters should be entered as UPPERCASE.

Do the letters have to be in UPPERCASE? Is this allowed:

abc 1234
aBc 1234
Abc 1234

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

Hmmm...

I replied to this thread through my "newsreader", OutLook Express, but for
whatever reason that reply didn't get posted.

So, I had to go through the discussion group website.

Ok, here goes...

This is quite ugly but it seems to work!

Create this defined name
Goto the menu Insert>Name>Define
Name: Letters
Refers to:

="ABCDEFGHIJKLMNOPQRSTUVWXYZ"

OK out

Assuming the range to validate is A1:A5

Select the entire range A1:A5
Goto the menu Data>Validation
Allow: Custom
Formula: (all on one line)

=AND(COUNT(FIND(MID(A1,ROW(INDIRECT("1:3"))
,1),Letters))=3,MID(A1,4,1)=" ",COUNT(--MID(A1,5,1)),
COUNT(-MID(A1,ROW(INDIRECT("5:"&LEN(A1))),1))
=LEN(A1)-4,LEN(A1)>=7,LEN(A1)<=10,
COUNTIF(A$1:A$5,A1)<=1)

**Uncheck** Ignore blank

OK out

Works based on these rules:

The first 3 characters must be UPPERCASE letters A to Z.

The 4th character must be a space character.

The 5th through the 10th characters must be the digits 0 to 9. This varies
due to the different lengths that your numbers can have 3, 4, 5 or 6 digits.

The max length of the entry must be <=10 characters.

There can be no duplicate entries in the range.

 

[Lilyput]

Biff, thanks for the formula and I think I nderatnd how this should work
however I am having difficulty getting it to work - I am gertting an error
message whatever three letters then a space then numbers I enter.

I have copied the formula as I have input it - as far as I can see except
for the cell references I have used the formula you sent me.

=AND(COUNT(FIND(MID(D2,ROW(INDIRECT("1:3")),1),LETTERS))=3,MID(D2,4,1)="
",COUNT(--MID(D2,5,1)),COUNT(-MID(D2,ROW(INDIRECT("5:"&LEN(D2))),1))=LEN(D2)-4,LEN(D2)>=7,LEN(D2)<=10,COUNTIF(D$2$4999,D2)<=1)

I have unchecked the ignore blanks box when entering the validation


CAn you see where I may be going wrong please?

 

[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