How do I prevent duplicate data in Excel?

P

PANKAJ KUMAR

I want to make a row which can't accept a duplicate entry ( jst as MS-Access
Primary Key Field).
Please suggest, as its urgent.

Thanks & regards

Pankaj
IS Executive
DTDC Courier & Cargo Ltd.
New Delhi
Mob. 9313003826
Phone:- 011-51514148, 51514197 Extn. 23
 
N

neopolitan

If you are entering data in one row across several columns and you want
to prevent duplicates from being entered in that one row (Assume Row 2
for this example):

Select the row, then click on Data>Validation and select "Custom" in
the dropdown list in the "Settings" tab. Then enter this formula in
the "Formula" window: =COUNTIF(2:2,A$2)<=1 .

Now an error message pops up whenever the user attempts to enter the
same data twice in Row 2.

If you want to prevent duplicates in a column (Assume Col A) instead
of a row then the formula to enter is: =COUNTIF(A:A,$A1)<=1 .
 
J

Jim Rech

Trying to make Excel work like Access or any other application can be a
challenge but you might try this:

Say you require that every entry in column A be unique. Select all of
column A by clicking the column A heading. Make sure that cell A1 is the
active cell. Then do a Data, Validation and pick Custom as the Allow type.
In the formula box enter: =COUNTIF(A:A,A1)=1

Customize the Alert tab to show the user a helpful error message.


--
Jim
|I want to make a row which can't accept a duplicate entry ( jst as
MS-Access
| Primary Key Field).
| Please suggest, as its urgent.
|
| Thanks & regards
|
| Pankaj
| IS Executive
| DTDC Courier & Cargo Ltd.
| New Delhi
| Mob. 9313003826
| Phone:- 011-51514148, 51514197 Extn. 23
 
Top