Data Validation, Identify Duplicates and Limit imput

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

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

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
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

The letters should be entered as UPPERCASE.

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
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




.

Hi,

Try to use the following formula in Data Validation Custom

=AND(CODE(LEFT(C13,1))=65,CODE(LEFT(C13,1))=90,C ODE(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

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Lilyput" wrote in message
...
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

That formula allows entries like:

ABC 1E100
ABC 1.5
ABC 1


--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote:

Hi,

Try to use the following formula in Data Validation Custom

=AND(CODE(LEFT(C13,1))=65,CODE(LEFT(C13,1))=90,C ODE(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

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Lilyput" wrote in message
...
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

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,C ODE(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)

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"T. Valko" wrote in message
...
That formula allows entries like:

ABC 1E100
ABC 1.5
ABC 1


--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote:

Hi,

Try to use the following formula in Data Validation Custom

=AND(CODE(LEFT(C13,1))=65,CODE(LEFT(C13,1))=90,C ODE(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

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Lilyput" wrote in message
...
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

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.

--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote in message
...
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,C ODE(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)

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"T. Valko" wrote in message
...
That formula allows entries like:

ABC 1E100
ABC 1.5
ABC 1


--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote:

Hi,

Try to use the following formula in Data Validation Custom

=AND(CODE(LEFT(C13,1))=65,CODE(LEFT(C13,1))=90,C ODE(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

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Lilyput" wrote in message
...
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

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

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.

--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote in message
...
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,C ODE(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)

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"T. Valko" wrote in message
...
That formula allows entries like:

ABC 1E100
ABC 1.5
ABC 1


--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote:

Hi,

Try to use the following formula in Data Validation Custom

=AND(CODE(LEFT(C13,1))=65,CODE(LEFT(C13,1))=90,C ODE(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

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Lilyput" wrote in message
...
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




.

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote in message
...
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,C ODE(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)

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"T. Valko" wrote in message
...
That formula allows entries like:

ABC 1E100
ABC 1.5
ABC 1


--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote:

Hi,

Try to use the following formula in Data Validation Custom

=AND(CODE(LEFT(C13,1))=65,CODE(LEFT(C13,1))=90,C ODE(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

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Lilyput" wrote in message
...
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




.

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Lilyput" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote in message
...
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,C ODE(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)

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"T. Valko" wrote in message
...
That formula allows entries like:

ABC 1E100
ABC 1.5
ABC 1


--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote:

Hi,

Try to use the following formula in Data Validation Custom

=AND(CODE(LEFT(C13,1))=65,CODE(LEFT(C13,1))=90,C ODE(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

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Lilyput" wrote in message
...
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




.