• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Validate duplicate Entry (Part of a text string)

vijay.vizzu

vijay.vizzu

Member
Hi...all,


Greetings of the day, Today i have an question that, i just want to validate the data to be entered duplicate. i want to restrict the data to be entered in duplicate.

1. excel has to check the left most 9 characters in a range,and if it is already exists in that range, then it can't be entered.

2. excel has to check, if the left most 1st character starts with 9, then it should extract 11 digits and has to be checked in that range, if it exists then not be entered.


So please help me.
 
=LEFT("1234567890987654321",9)


That will give you the left-most 9 characters.


=IF(LEFT(Text,1)="9",LEFT(Text,11),LEFT(Text,9))


The cell above will contain the first 11 characters of Text if the first character is equal to 9.


Note that there is no error checking, and note that the number is being treated as text. You will need to convert the number to text for the "LEFT" function to work.


That should get you started.


Don
 
Thank you Don Minter, i have done this in another column, and put countif function to retrieve duplicate values, but i need to do this both ideas in Data validation. so how it can be done to prevent the duplicate values to be entered that column


Any help highly appreicated
 
The formula is going to get very complicated, so I suggest you start by writing parts of it in various cells to make sure each part works properly.


I have given you the formula for getting either 9 or 11 digits/characters already.


You will then need a "MATCH" function to check this value against a range of values.


Next, you'll need to compare only the first 9 or 11, depending on the value entered, so you'll need to repeat the formula above for the array. I don't know if the MATCH function will allow this. I think you may be better off setting up two helper columns near the array data, one with 9 digits, and the other with 11 digits, then comparing the entered data (using "MATCH") to whichever array is appropriate.


Lastly, if you're doing this with a dynamic comparison list, you should probably do this on a table, and use the "ROWS" function to determine the length of the array.


Why must you do this with data validation? I think it would be easier to write VBA code to do the comparison. The formula in the data validation custom field is going to be a horror to get right and troubleshoot.


Don
 
You must log in or register to reply here.
Back
Top