If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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" 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 |
#3
|
|||
|
|||
Data Validation, Identify Duplicates and Limit imput
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 . |
#4
|
|||
|
|||
Data Validation, Identify Duplicates and Limit imput
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 |
#5
|
|||
|
|||
Data Validation, Identify Duplicates and Limit imput
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 |
#6
|
|||
|
|||
Data Validation, Identify Duplicates and Limit imput
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 |
#7
|
|||
|
|||
Data Validation, Identify Duplicates and Limit imput
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 |
#8
|
|||
|
|||
Data Validation, Identify Duplicates and Limit imput
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 . |
#9
|
|||
|
|||
Data Validation, Identify Duplicates and Limit imput
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 . |
#10
|
|||
|
|||
Data Validation, Identify Duplicates and Limit imput
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 . |
Thread Tools | |
Display Modes | |
|
|