Data validation - restrict duplicate value

[Michelle]

Hi!

How to set data validation function (in column B) to restrict user from
entering same invoice number from same supplier?
Column A: Supplier
Column B: Supplier Invoice Number

TIA

 

[Jacob Skaria]

Using data validation you can; but only manual entries/dropdown entries will
be validated

--Select B2:B10
--Select menu Data>Validation
--Select Custom from the 'Allow' dropdown and enter the below formula

=COUNTIF($B$2:$B$10,B$2)=1
--From the Error alert tab enter the message you want to be displayed
--Click OK and try.

If you are selecting the entire column the validatio would be
=COUNTIF(B:B,$B$1)=1


If this post helps click Yes

 

[Jacob Skaria]

Correction to the formulas

=COUNTIF($B$2:$B$10,B2)=1

and for entire column

=COUNTIF(B:B,B1)=1

If this post helps click Yes

 

[T. Valko]

I interpret the post differently.

Supplier1...1 = ok
Supplier2...1 = ok
Supplier1...1 = not ok
..................1 = not ok

Different suppliers having the same invoice number should be a valid entry.
Although the chances of that happening might be pretty slim!

Assume the range to validate is B2:B10
Select the range B2:B10 starting from cell B2
Data>Validation
Allow: Custom
Formula:

=AND($A2<>"",SUMPRODUCT(--($A$2:$A2&"^^"&$B$2:B2=A2&"^^"&B2))<2)

Uncheck Ignore blank
OK

 

[T. Valko]

=AND($A2<>"",SUMPRODUCT(--($A$2:$A2&"^^"&$B$2:B2=A2&"^^"&B2))<2)

And to be consistent, the column refs don't need to be absolute:

=AND(A2<>"",SUMPRODUCT(--(A$2:A2&"^^"&B$2:B2=A2&"^^"&B2))<2)

 

[Michelle]

thanks for your replies!

This is exactly what i want, sorry for not stating my question clearly in
the first place
=AND(A6<>"",SUMPRODUCT(--(A$2:A6&"^^"&B$2:B6=A6&"^^"&B6))<2)

valko, would you care to explain the formula used?

 

[T. Valko]

would you care to explain the formula used?

The formula first checks to see if a supplier name has been entered in A6:

A6<>""

If A6 is an empty cell then you can't enter an invoice number in B6.

This portion checks to make sure the *combination* of the supplier name and
the invoice number are unique therefore, no duplicates:

SUMPRODUCT(--(A$2:A6&"^^"&B$2:B6=A6&"^^"&B6))<2

It concatenates the supplier name and the invoice number together separated
by the characters ^^. It would look something like this:

Supplier1^^0001

Let's assume A2:B2 holds:

Supplier1...0001

If you try to enter in A6:B6 Supplier1 and 0001 then:

SUMPRODUCT(--(A$2:A6&"^^"&B$2:B6=A6&"^^"&B6))<2

Would evaluate to FALSE and would not allow you to enter that invoice number
in B6.

 

[Michelle]

Thanks Biff, you are great!!

The formula first checks to see if a supplier name has been entered in A6:

A6<>""

If A6 is an empty cell then you can't enter an invoice number in B6.

This portion checks to make sure the *combination* of the supplier name and
the invoice number are unique therefore, no duplicates:

SUMPRODUCT(--(A$2:A6&"^^"&B$2:B6=A6&"^^"&B6))<2

It concatenates the supplier name and the invoice number together separated
by the characters ^^. It would look something like this:

Supplier1^^0001

Let's assume A2:B2 holds:

Supplier1...0001

If you try to enter in A6:B6 Supplier1 and 0001 then:

SUMPRODUCT(--(A$2:A6&"^^"&B$2:B6=A6&"^^"&B6))<2

Would evaluate to FALSE and would not allow you to enter that invoice number
in B6.

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

You're welcome. Thanks for the feedback!