Data validation - restrict duplicate value

  • Thread starter Thread starter Michelle
  • Start date Start date
M

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
 
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
 
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
 
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
 
=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)
 
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?
 
=AND(A6 said:
would you care to explain the formula used?
Click to expand...

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.
 
Thanks Biff, you are great!!

T. Valko said:
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





.
Click to expand...
 
Back
Top