Data validation - restrict duplicate value

Michelle

unread,

Nov 25, 2009, 1:25:01 AM11/25/09

to

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

unread,

Nov 25, 2009, 2:05:02 AM11/25/09

to

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

Jacob Skaria

unread,

Nov 25, 2009, 2:07:01 AM11/25/09

to

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
---------------
Jacob Skaria

T. Valko

unread,

Nov 25, 2009, 2:26:31 AM11/25/09

to

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

--
Biff
Microsoft Excel MVP

"Jacob Skaria" <Jacob...@discussions.microsoft.com> wrote in message
news:7CF69D7F-32C3-474E...@microsoft.com...

T. Valko

unread,

Nov 25, 2009, 2:34:49 AM11/25/09

to

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

--
Biff
Microsoft Excel MVP

"T. Valko" <biffi...@comcast.net> wrote in message
news:OoI9cCab...@TK2MSFTNGP05.phx.gbl...

Michelle

unread,

Nov 26, 2009, 1:55:01 AM11/26/09

to

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" wrote:

> .
>

T. Valko

unread,

Nov 26, 2009, 12:03:40 PM11/26/09

to

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

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

--
Biff
Microsoft Excel MVP

"Michelle" <Mich...@discussions.microsoft.com> wrote in message
news:F65DB813-E9CB-4ED0...@microsoft.com...

Michelle

unread,

Dec 1, 2009, 12:47:01 AM12/1/09

to

Thanks Biff, you are great!!

"T. Valko" wrote:

> .
>

T. Valko

unread,

Dec 1, 2009, 1:35:17 AM12/1/09

to

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"Michelle" <Mich...@discussions.microsoft.com> wrote in message

news:05E67E23-BBBD-403E...@microsoft.com...