Formula to hightlight duplicated entry

**Paitry** · 10-08-2009, 04:56 PM

Hello,
Im a newby to excel and learning formulas at present.

-I have a column in spread sheet.
-The purpose of this column is to enter a product code on each row of this column.
-The column in theory should only have each product number entered once.
-So i am trying to find a formula to avoid any code being entered more than once.
-As we are visually checking this column to make sure that the product code is only entered once, we have had a few instances of human error, so i am looking for a formula that will highlight the code in red if it has already been entered ( so highlighted in red will be both the same codes, so visually we will be able to identify code entry error)

- I hope this is clear as im new to excel. Any help would be much appreciated.

Cheers

Joel

**teylyn** · 10-08-2009, 05:02 PM

Hi,

with your code in column A, use this in conditional formatting for column A

=COUNTIF($A:$A,A1)>1

Step by Step:

Select column A by clicking the column header
Click Format - Conditional Formatting
Select "Formula is" in the first box
enter the formula in the formula box
select a format
hit ok

**NBVC** · 10-08-2009, 05:03 PM

Select the range and go to Format|Conditional Formatting

Select Formula Is from the first dropdown menu and enter formula:

=Countif($A$1:$A$100,$A1)>1

where A1:A100 is the range you selected with A1 being at the top. Adjust as necessary.

Click Format and choose colour from Pattern tab.

**Paitry** · 10-08-2009, 05:49 PM

I have just tried the formula on a blank sheet. it works fine.

When i ad it to my existing sheet, into my scrolling coloumn it doesnt work so well.

Any ideas?

Your help would be much appreciated

Joel