Hello,
I am looking to create a unique Customer ID using 1) 8 digits and an optional single letter at the end, 2) with dashes after the third and fifth digits, and 3) ensure there are no duplicates. For example, "12345678A" would be shown as "123-45-678A" with no duplicates allowed. Or, "12345678" would be shown as "123-45-678" with no duplicates allowed.
I would like to avoid combining two cells, if possible.
Do you want something that will generate those numbers, or as your title says, something that will do data validation to ensure that a number typed in will conform to those rules?
Data validation; to ensure that a number manually typed in conforms to those rules.
Pl see attached file. A2:a14 is validated.
Does case matter?Originally Posted by b624333
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Normally I don't download files but in this case I did!
Your formula allows entries like:
1E3-45-6.8A
1.3-45-.08A
1.3-45--10A
@Tony Valko ,
I have corrected the formula.
For A2 ,
Please Login or Register to view this content.
Last edited by kvsrinivasamurthy; 06-16-2014 at 05:08 AM.
That formula won't accept entries like:
123-45-678
Here's my offering. Assuming the letter can be any case.
=AND(COUNT(-MID(A5,ROW(INDIRECT("1:3")),1))=3,MID(A5,4,1)="-",COUNT(-MID(A5,ROW(INDIRECT("5:6")),1))=2,MID(A5,7,1)="-",COUNT(-MID(A5,ROW(INDIRECT("8:10")),1))=3,OR(LEN(A5)=10,LEN(A5)=11),IF(LEN(A5)=11,SEARCH("~"&RIGHT(A5),"abcdefghijklmnopqrstuvwxyz"),1))
If the letter needs to be in uppercase:
=AND(COUNT(-MID(A5,ROW(INDIRECT("1:3")),1))=3,MID(A5,4,1)="-",COUNT(-MID(A5,ROW(INDIRECT("5:6")),1))=2,MID(A5,7,1)="-",COUNT(-MID(A5,ROW(INDIRECT("8:10")),1))=3,OR(LEN(A5)=10,LEN(A5)=11),IF(LEN(A5)=11,FIND(RIGHT(A5),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),1))
If you test those formulas on the worksheet they must be array entered.
Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Tony's point is well taken. kvsrinivasamurthy's formula doesn't allow for entry of things like 123-45-678. However, Tony, I cannot seem to get your formula to work. I have never used an array formula, much less for a custom data validation formula. Could you explain this to me further?
Ooops!
I completely forgot this requirement:
When I was writing the formula in post #8 I was thinking to myself: man, this would be much easier if we could use some type of regular expression pattern recognition. So, I posted about this in the MS MVP Discussion List.with no duplicates allowed
MS Excel MVP Brad Yundt came up with a good idea of using array constants that will significantly reduce the formula length but we have to use defined names as array constants are not allowed in data validation formulas.
So, if you can answer this question:
Does the optional letter have to be in uppercase?
Then we can finish the formula.
Try this for A2
Please Login or Register to view this content.
That allows entries like:
123-45-67
123-45-6
123-45-
One more trail for A2
Please Login or Register to view this content.
Yes, it would have to be uppercase. Thank you for your help.
Try this...
First, create these defined names.
Goto the Formulas tab>Define Name
Name: Chars1
Refers to: ={1,2,3,5,6,8,9,10}
Name: Chars2
Refers to: ={4,7}
Name: Letters
Refers to: ="ABCDEFGHIJKLMNOPQRSTUVWXYZ"
OK out
Now setup the validation.
Let's assume you want to apply the data validation to the range A2:A10.
Select the entire range A2:A10.
Goto the Data tab>Data Validation
Allow: Custom
Formula:
=AND(COUNTIF(A$2:A$10,A2)<2,COUNT(-MID(A2,Chars1,1))=8,MID(A2,Chars2,1)="-",FIND(MID(A2&"A",11,1),Letters),LEN(A2)<12)
Uncheck: Ignore Blank
OK out
Try it out.
Last edited by Tony Valko; 06-18-2014 at 03:49 PM.
That is absolutely amazing. Well done. Works perfectly. Only allows values in the format of ###-##-### or ###-##-###X, with no duplicates.
You're welcome. Thanks for the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
There's a problem. The data validation above works great, until I save and close. If I reopen and wish to add more "Customer IDs", the data validation doesn't work (i.e. it doens't let anything get entered into the cell).
I can't figure out why! Why would this be?
I attached an example workbook... Try it for yourself. You can't enter anything. But when you start from scratch it works fine, until you save and close.
HELP!
Hmmm...
The file does as you say in Excel 2007 and Excel 2010.
However, when I create the same file in Excel 2002 (*.xls file format) it works as expected, no problems whatsoever.
Further, when I open the *.xls file in Excel 2010 it works as expected, no problems whatsoever.
I'm completely baffled.
Let me try to get some other folks to have a look at this and see what they can come up with.
Both lower and upper case letters will be accepted.IF only upper case is required
Please Login or Register to view this content.
That allows entries like:
123-45-6e2
123-45-e10
123-4e-002
123-45-6e2A
Not having much luck trying to figure out what the problem is.
The formula works, it's the application of the validation that is causing the problem. I don't know if this is some kind of bug or what's causing it.
A couple of alternatives...
1. Use a helper column. For example, if you want to apply the validation to the range A2:A10, array enter the formula in B2 and copy down to B10.
Then, as the validation rule use:
Allow: Custom
Formula: =B2=TRUE
You can hide the helper column if you want.
Tested this and it does work as expected. Saving the file, closing it then reopening it and the validation still works as expected.
2. Use an event macro. The macro runs when a change is made to the cell. It would test that the entry meets the conditions. If it does it allows the entry. If it doesn't meet the conditions then it would pop up a message box letting the user know that what they tried to enter is an invalid entry.
Unfortunately, I'm not much of programmer so I can't help you option 2. You could start a new thread in the Excel Programming / VBA / Macros forum and include a link to this thread so folks can see what was already discussed/tried.
@Tony Valko
Hats Off to your patience.
Using Helper cell or Worksheet event is an alternative. I wanted to get in normal way using CUSTOM.I have given one more trail now. Pl Check.
Please Login or Register to view this content.
Last edited by kvsrinivasamurthy; 06-30-2014 at 03:41 AM.
I don't know why, but if you break the 8 number part of the validation out into a separate named formula:
=COUNT(MID(Sheet1!A2,{1,2,3,4,5,6,7,8,9,10},1)+0)=8
and then use:
=AND(COUNTIF(A:A,A2)<2,Is8Nums,MID(A2,4,1)="-",MID(A2,7,1)="-",ISNUMBER(FIND(MID(A2&"A",11,1),Letters)),LEN(A2)<12)
it seems to work.
Remember what the dormouse said
Feed your head
Interesting.
Is this documented?
Regards
Last edited by XOR LX; 06-30-2014 at 05:25 AM.
I don't know, because I don't know why it's necessary, which makes it hard to search for a documented bug (which is hard enough anyway!).
Good job!
One thing to be careful about...
When defining Is8Nums make sure cell A2 is the active cell.
I was kind of suspicious about the array constants but setting the validation to use my original monster formula (with a couple of tweaks) that doesn't use array constants also had the same problems in Excel 2007+.
Man, I hate not knowing why things don't work when they should.
Well, you know who to ask...
On the few occasions when I've specifically asked "them" for comment on other issues I got none!
Agreed. This one is a bit of a mystery...Man, I hate not knowing why things don't work when they should.
Then go ask, my friend! Go ask!Well, you know who to ask...
Well, we can but try.Although if it is a bug, I estimate the chances of it being fixed at a fairly round number...
Still allows entries like:
123-45-6E1
123-45-E01
123-4E-001
123-E0-001
12E-00-001
1E0-00-001
Thank you so much! Looks like we all learned something new... I certainly did. Thank you for all your help, patience, and commitment.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks