Thread: Data Validation, Identify Duplicates and Limit imput

>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" <Lilyput@discussions.microsoft.com> wrote in message
news:9B94CF57-DA0D-4918-9ECF-07FEED586247@microsoft.com...
>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" <Lilyput@discussions.microsoft.com> wrote in message
> news:9B94CF57-DA0D-4918-9ECF-07FEED586247@microsoft.com...
> >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
> >
> >
>
>
> .
>

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.

--
Biff
Microsoft Excel MVP


"Lilyput" wrote:

> 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" <Lilyput@discussions.microsoft.com> wrote in message
> > news:9B94CF57-DA0D-4918-9ECF-07FEED586247@microsoft.com...
> > >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
> > >
> > >
> >
> >
> > .
> >

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),LET TERS))=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?

"T. Valko" wrote:

> >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.
> >="ABCDEFGHIJKLMNOPQRSTUVWXYZ"
>
> Ok, now I see why my newsreader reply didn't get posted!
>
> There's that "post-killing" string XYZ.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "T. Valko" wrote:
>
> > 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.
> >
> > --
> > Biff
> > Microsoft Excel MVP
> >
> >
> > "Lilyput" wrote:
> >
> > > 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" <Lilyput@discussions.microsoft.com> wrote in message
> > > > news:9B94CF57-DA0D-4918-9ECF-07FEED586247@microsoft.com...
> > > > >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" <Lilyput@discussions.microsoft.com> wrote in message
news:9B94CF57-DA0D-4918-9ECF-07FEED586247@microsoft.com...
> 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" <Lilyput@discussions.microsoft.com> wrote in message
> news:9B94CF57-DA0D-4918-9ECF-07FEED586247@microsoft.com...
> > 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" <TValko@discussions.microsoft.com> wrote in message
news:F14DF69F-A33E-4A63-A64F-8FDBA27761BE@microsoft.com...
> 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" <Lilyput@discussions.microsoft.com> wrote in message
>> news:9B94CF57-DA0D-4918-9ECF-07FEED586247@microsoft.com...
>> > 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" <mathurashish@hotmail.com> wrote in message
news:2760F9A8-D5F7-4D4C-A754-75495178D50E@microsoft.com...
> 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" <TValko@discussions.microsoft.com> wrote in message
> news:F14DF69F-A33E-4A63-A64F-8FDBA27761BE@microsoft.com...
>> 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" <Lilyput@discussions.microsoft.com> wrote in message
>>> news:9B94CF57-DA0D-4918-9ECF-07FEED586247@microsoft.com...
>>> > 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
>>> >
>>> >