Duplicate entries

[AJPPendrag]

I want to ensure duplicate entries do not occur in varios cells in a row. I know you can accomplish data validation (ISERROR(MATCH . . . .see below for full details) for a range of cells in a list - but can it be done for a row of data - and particular cells in that row

Thank

Andre

SUMMAR
This article explains how to use data validation to prevent the creation of duplicate entries in a list.
MORE INFORMATIO
The following example uses data validation to prevent duplicate entries from being entered within the range of cells A1 through A50. To create the example, you need set up two validation rules: one for the first cell in the list, and one for the rest of the cells. To do so, follow these steps:
1. Save and close any open workbooks, and then create a new workbook.
2. Select cell A1 and click Validation on the Data menu.
3. In the Allow list, click Custom, and then type the following formula in the Formula box:
=ISERROR(MATCH(A1,A2:A50,0)
4. Click the Error Alert tab, type the error message title that you want the user to see in the Title box, and then type the error message that you want the user to see in the Error Message box.
5. In the Style list, click Stop, and then click OK to apply the validation rule to cell A1.
6. Select cells A2:A50, and then, on the Data menu, click Validation.
7. On the Settings tab, click Custom in the Allow list, and then type the following formula in the Formula box:
=ISERROR(MATCH(A2,INDIRECT("$A$1:$A$"&ROW()-1),0))
8. Click the Error Alert tab. In the Title box, type the error message title that you want the user to see, and then in the Error Message box, type the error message that you want the user to see.
9. In the Style list, click Stop, and then click OK to apply the validation rule to cells A2:A50
As you make entries into cells A1:A50, Excel applies the validation rules that you have established. If you make a duplicate entry, you receive an error message. Clicking Retry allows you to change the cell entry, and clicking Cancel removes the cell entry.

 

[Frank Kabel]

Hi
use the following formula in the data validation dialog
(applies for row 2):

=COUNTIF($2:$2,A2)=1

-----Original Message-----
I want to ensure duplicate entries do not occur in varios

cells in a row. I know you can accomplish data validation
(ISERROR(MATCH . . . .see below for full details) for a
range of cells in a list - but can it be done for a row of
data - and particular cells in that row.

Thanks

Andrew

SUMMARY
This article explains how to use data validation to

prevent the creation of duplicate entries in a list.

MORE INFORMATION
The following example uses data validation to prevent

duplicate entries from being entered within the range of
cells A1 through A50. To create the example, you need set
up two validation rules: one for the first cell in the
list, and one for the rest of the cells. To do so, follow
these steps:

1. Save and close any open workbooks, and then create a new workbook.
2. Select cell A1 and click Validation on the Data menu.
3. In the Allow list, click Custom, and then type the

following formula in the Formula box:

=ISERROR(MATCH(A1,A2:A50,0))
4. Click the Error Alert tab, type the error message

title that you want the user to see in the Title box, and
then type the error message that you want the user to see
in the Error Message box.

5. In the Style list, click Stop, and then click OK

to apply the validation rule to cell A1.

6. Select cells A2:A50, and then, on the Data menu, click Validation.
7. On the Settings tab, click Custom in the Allow

list, and then type the following formula in the Formula
box:

=ISERROR(MATCH(A2,INDIRECT("$A$1:$A$"&ROW()-1),0))
8. Click the Error Alert tab. In the Title box, type

the error message title that you want the user to see, and
then in the Error Message box, type the error message that
you want the user to see.

9. In the Style list, click Stop, and then click OK

to apply the validation rule to cells A2:A50.

As you make entries into cells A1:A50, Excel applies the

validation rules that you have established. If you make a
duplicate entry, you receive an error message. Clicking
Retry allows you to change the cell entry, and clicking
Cancel removes the cell entry.

 

[Debra Dalgleish]

To check specific cells in a row, you can use the AND function to check
the ranges, or COUNTIF for a contiguous range of cells. For example, to
apply data validation to cell A2, use a formula similar to one of the
following:

=COUNTIF(D2:L2,A2)=0
or
=AND(COUNTIF(D2:F2,A2)=0,COUNTIF(I2:J2,A2)=0,L2<>A2)