Data validation - restrict duplicate value
 

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

Data validation - restrict duplicate value
 

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

--Select B2:B10
--Select menu DataValidation
--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


"Michelle" wrote:

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

Data validation - restrict duplicate value
 

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


"Jacob Skaria" wrote:

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

--Select B2:B10
--Select menu DataValidation
--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


"Michelle" wrote:

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

Data validation - restrict duplicate value
 

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
DataValidation
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" wrote in message
...
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


"Jacob Skaria" wrote:

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

--Select B2:B10
--Select menu DataValidation
--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


"Michelle" wrote:

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

Data validation - restrict duplicate value
 

=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" wrote in message
...
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
DataValidation
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" wrote in message
...
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


"Jacob Skaria" wrote:

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

--Select B2:B10
--Select menu DataValidation
--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


"Michelle" wrote:

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

Data validation - restrict duplicate value
 

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:

=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" wrote in message
...
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
DataValidation
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" wrote in message
...
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


"Jacob Skaria" wrote:

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

--Select B2:B10
--Select menu DataValidation
--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


"Michelle" wrote:

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




.

Data validation - restrict duplicate value
 

=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" wrote in message
...
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:

=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" wrote in message
...
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
DataValidation
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" wrote in message
...
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


"Jacob Skaria" wrote:

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

--Select B2:B10
--Select menu DataValidation
--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


"Michelle" wrote:

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




.

Data validation - restrict duplicate value
 

Thanks Biff, you are great!!

"T. Valko" wrote:

=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" wrote in message
...
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:

=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" wrote in message
...
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
DataValidation
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" wrote in message
...
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


"Jacob Skaria" wrote:

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

--Select B2:B10
--Select menu DataValidation
--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


"Michelle" wrote:

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




.



.

Data validation - restrict duplicate value
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Michelle" wrote in message
...
Thanks Biff, you are great!!

"T. Valko" wrote:

=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" wrote in message
...
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:

=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" wrote in message
...
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
DataValidation
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" wrote in
message
...
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


"Jacob Skaria" wrote:

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

--Select B2:B10
--Select menu DataValidation
--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


"Michelle" wrote:

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




.



.