Prevent Duplicate Partial Text String in Two Columns

[gmbhbls]

I have two columns in my Worksheet. Each column has Data Validation to ensure that the user enters the information in a specific format. The only "variable" that exists is the last three numeric characters. These last three characters may only be used once, in either of the two columns.


I've tried evaluating column values, using MATCH, and coming up with a number. If the number was greater than zero, triggering a Data Validation that shows an error. The only problem is that Data Validation only works on USER ENTRY, not evaluated values.


Does anyone know of a way to keep the Data Validation in place while preventing the last three numbers from being used more than once?

The two column Data Validations are listed below.

I thank you in advance for all of your time and effort!


=AND(LEN(C5)=12,EXACT(LEFT(C5,9),"120R0343-"),ISNUMBER(-MID(C5,ROW(INDIRECT("10:12")),1)))
=AND(LEN(D5)=12,EXACT(LEFT(D5,9),"110R0343-"),ISNUMBER(-MID(D5,ROW(INDIRECT("10:12")),1)))

 

[gmbhbls]

I was able to get your code to work from post 5. I realized that your cell references were wrong. When I changed the three C5 to D5, everything fell into place. It works perfectly!

Thank you so much for your help and patience! This is amazing!

Column D
=AND(LEN(D5)=12,EXACT(LEFT(D5,9),"110R0343-"),ISNUMBER(-MID(D5,ROW(INDIRECT("10:12")),1)),COUNTIF($C$5:$D$100,"???R0343-"&RIGHT(D5,3))=1)

 

[Marcelo Branco]

Great!
You are very welcome. Glad to help

And sorry for forgetting to correct the reference for D5 in the second formula. I was answering several questions and simply copied the formula of column C. It happens some times because i answer too fast and go over some details...

M.