OK so here is an example of the data that I need inforced: AA88A88aaAA8888
I need a way to inforce that it is not a duplicate of any in the column either. I'm currently using a VLOOKUP to validate, but any solution that can give an end result would be much apriciated. Also if there is a way to inforce correct case of the letters.
Thanks guys
Formula:
Please Login or Register to view this content.
Adapt the range
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
I put it in the validation but didn't trigger anything when i put in the wrong data. Can it be added to this?
=ISNA(VLOOKUP(A3,A$3:A2,1,FALSE))
Regret for the inconvenience.
Select A2 cell to A100 and in validation apply the below formula.
Formula:Please Login or Register to view this content.
The above formula refers the active cell as A2 so the active cell should be A2 in your selection. Active cell will have a white background after selection also.
In fact I chosen the sumproduct for using exact function then only we can able to used the formula in Validation since Validation don't accept array Formulas. So vlookup or some other function cannot be used for exact match without an array notion.
Hem, still not triggering. Maybe I didn't explain it right, let me give an example:
I need an error to occure or the data must be rejected some how when a letter number combination does not match the pattern, and must not be a duplicate of any in the column.
BA10AbbAB0001
AA10AbbAB0001
AA10AbbAB0002
AA10AbbAB0003
AA10AbbAB0003 *Error
AA10AbbAB004 *Error
AA10Abb440001 *Error
so, Input MUST = CAP/CAP/#/#/CAP/lc/lc/CAP/CAP/#/#/#/# ?
AND should always be that length?
THIS is why sample wb's (with no personal/confidential info included, and expected results) make answers easier...we can not tell from the initial post what you want...
-Edit-
for example:
AA10AbbAB0003
AA10AbbAB0003 *Error
why is first valid, yet second invalid? duplicate or...???
Last edited by dredwolf; 11-16-2012 at 01:41 AM.
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
I am unable to make it without a helper column since restriction in Validation Formula Length.Originally Posted by Talo
In B2 cell
Formula:Please Login or Register to view this content.
Select From A2 to A100 and ensure that A2 is active cell in your selection. Now in Validation Formula
Formula:Please Login or Register to view this content.
Refer the attached excel for details.
After finishing all workings and when I come back to post I noted the case sensitivity in your post, but I don't want to go back and look the lengthy formula again for correction. We wait and see whether OP need that criteria also getting added in the formula, if so then the suggested formula needs to be altered again
NO Problem
was just trying to get some confirmation of data for you
(Edit)
Hem, I see what you did, but if I just mouse click some were else, I get a FALSE with no error, and I can just hit cancel and the bad data stays. Also when I try to fix it, if I click away I get an error even though it's correct, but works if I hit enter. Any way to fix that?
(Edit)
Sorry if I am making myself unclear
"why is first valid, yet second invalid? duplicate or...??? "
-- yes, it's a duplicate
I got tasked with something so give me a day to test it out. Here is some actual data:
AF10A01aaLF0012
AF10A01aaLF0013
AF10A02aaLF0013
AF10A01aaBB0212
AF10A01aaLF0012
SO11B02eePS0001
SD12A03eeBB0019
SO12E01eePS0001
SO12E01eePS0005
SO12E01eePS0002
SO12E01eePS0003
SO12E01eePS0004
S012E04eeNV0001 - Error due to 2nd character being a Zero instead of the letter O
SO12E01eePS002 - Error due to not correct format, missing a number at end of string
SO12E01EEPS0055 - Error due to incorrect case of ee
SO12E01eePS0001 - Error due to duplication
Last edited by Talo; 11-17-2012 at 08:05 AM.
Revised B2 cell formula
Formula:Please Login or Register to view this content.
Drag it down.
The above formula referenced for 100 rows and perform calculation based on the reference so if you would like to apply it for more rows then resize it to your desired range.
Added some if functions to make the formula run faster comparing to previous suggestion.
Regarding the Cancel button issue – I tested it and it happens when you do COPY PASTE and after that if you do any change then it alerts you for wrong input and clicking the cancel button accepts the wrong entry. But it wont accept wrong entry if you type it MANUALLY instead of copy paste. If you type it manually then it will clear the wrong entry from the cell when pressing Cancel button.
If you would like to restrict the users from doing copy paste from the Column-A then add the below code in sheet module which will disable doing copy paste in column-A data.
Do right click in Sheet Tab and copy and paste the below code.
Refer the attached files for details.
Please Login or Register to view this content.
Hope this helps!
I dont see why you dont just use A:A instead of A1:A100, but it works. Thank you for all your hard work. I am some what supprised what you had to do to get it to work, and see why I was having problems figuring it out on my own.
Problem Solved, Thank You
Thanks for your feedback!
The reason for limiting the range reference to A1:A100 is that the excel version which is mentioned nearby your name is excel 2003 and we cannot refer Sumproduct or any other array formula’s for whole column upto excel 2003 version. Later versions supports whole column reference and if your profile update is outdated and your excel version is higher than 2003 then you can refer it to whole column.
If your’s is excel is 2003 then you have to stick with range reference instead of whole column reference and you can almost refer to the whole column by limiting the range reference to 65535 rows like $A$1:$A$65535 instead of A1:A100.
Glad to help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks