Duplicate values in Excel

[Louise]

Hi all

is there an easy way to ask Excel to recognise duplicate
values on a worksheet? For example, I have a large
worksheet containing hundreds of entries of staff names
which is updated with new starters. However, I need to
ensure that if I/colleague try to enter a name onto the
worksheet that already exists, it will come up with an
Error message.

What is the easiest way to do this??

Thanks.

Louise

 

[Arvi Laanemets]

Hi

Data validation

An example:
You want make entries in column A unique. Select column A, activate data
validation window (Data.Validation from menu), select Custom, and enter the
formula
=(COUNTIF(A:A,A1)<2)
Set behaviour for case the condition will be false, and error message. Press
OK.

Now, whenever you enter something, which was entered into some other cell in
column A before, you get an error message, and you are given the opportunity
to retype the entry, ot to cancel.

 

[NH]

Select the entire sheet or column whereever the name would be entered by
you/colleague
Using Data Validation select custom from drop down and type the following
==COUNTIF($A:$Z,A1)=1
This will give an error message whenever same name is entered more than
once.

 

[Louise]

Excellent!

Thanks very much.

Louise

-----Original Message-----
Hi

Data validation

An example:
You want make entries in column A unique. Select column A, activate data
validation window (Data.Validation from menu), select Custom, and enter the
formula
=(COUNTIF(A:A,A1)<2)
Set behaviour for case the condition will be false, and error message. Press
OK.

Now, whenever you enter something, which was entered into some other cell in
column A before, you get an error message, and you are given the opportunity
to retype the entry, ot to cancel.


--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)






.