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)))

 

[Marcelo Branco]

Maybe you can use Conditional Formatting --> Highlight Duplicates

M.

 

[gmbhbls]

Thanks, Marcelo,

Conditional formatting wouldn't work. It would still allow the user to enter a duplicate number at the end of their entry. The two different columns have different prefixes. If they entered 120R0343-001 or 110R0343-001, conditional formatting would still see them as different values.

Maybe you can use Conditional Formatting --> Highlight Duplicates

M.

 

[Marcelo Branco]

I misunderstood your question - thought you wanted to prevent duplicates (last 3 digits) on each column, not in both columns.
Re-thinking

M.

 

[Marcelo Branco]

Maybe adding a new condition in the formula (in blue). I used the range $C$5:$D$100 - adjust to suit

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

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

M.

 

[gmbhbls]

Marcello,

Thank you! It almost works... See the attached graphic for reference (link below).

Note: The red is a conditional format that's been applied to show the user that they are not permitted to enter a value into the opposite column if they enter a value into a specific column.

If I enter 120R0343-123 into C5, it still allows me to enter 110R0343-123 into any cell in column D. I essentially want to "use up" the last three digits so that they can't be used again.

On the other hand, if I try to enter 120R0343-124 into cell C8, with cell D7 having the entry 110R0343-124, it does not permit it.

That's the functionality that I'm looking for.

I'm looking for something like a checkbook where you can only use a check number once. In this case, the last three digits.

I will continue to tweak your contribution to see if it can be modified to fit these circumstances.

https://drive.google.com/open?id=1N9hJfWwqzp7qjXdmCWJP2sVz7kxygHtB

Maybe adding a new condition in the formula (in blue). I used the range $C$5:$D$100 - adjust to suit

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

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

M.

 

[Marcelo Branco]

The formula i suggested is to be used in Data Validatiion

In Conditional Formatting try

Column C
=AND(C5<>"",NOT(AND(LEN(C5)=12,EXACT(LEFT(C5,9),"120R0343-"),ISNUMBER(-MID(C5,ROW(INDIRECT("10:12")),1)),COUNTIF($C$5:$D$100,"???R0343-"&RIGHT(C5,3))=1)))
Fill--> red

Column D
=AND(D5<>"",NOT(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)))
Fill--> red

M.

 

[gmbhbls]

Marcelo,

I'm sorry, I am using Data Validation to try to solve the problem.

I mentioned, as an aside, that the red cells were controlled by conditional formatting. They have no impact on what I am trying to accomplish.

I am still looking for a solution using Data Validation.

 

[Marcelo Branco]

Worked for me

	C​	D​
4​
5​	120R0343-123​
6​		110R0343-123​
7​		110R0343-124​

<tbody>
</tbody>

M.

 

[Marcelo Branco]

Marcelo,

I'm sorry, I am using Data Validation to try to solve the problem.

I mentioned, as an aside, that the red cells were controlled by conditional formatting. They have no impact on what I am trying to accomplish.

I am still looking for a solution using Data Validation.

The formulas in post 5 worked perfectly for me in Data Validation to prevent the users to enter invalid data.

The formulas in post 7 are to be used in CF and worked perfectly for me to highlight (see post 9) wrong entries (before the DV in place)

M.