Excel 2007 : Detect a duplicate value in a column / range

**lmsexcelforum** · 05-28-2009, 06:41 AM

Hi

I want to flag if a value in a range of data is a duplicate for example:

Control ID
001 <----- this is a duplicate
002
003
004
001 <----- this is a duplicate

Is there a way of colouring these cells red (or whatever) to highlight that they are not unique within the [Control ID] column ?? I've thought about Conditional Formatting but not sure if that will do it on it's own - or if I'll need some VB or another column with a formula in it to check for the uniqueness.

I tried:
=IF(ISNA(MATCH(tblBusinessTerms[[#This Row],[Control ID]],[Control ID],FALSE)),"No","Yes")

but of course the answer is always "Yes" because it does exist in the range....it isn't looking for uniqueness, only if it exists.

Thanks in advance

**DonkeyOte** · 05-28-2009, 06:42 AM

Perhaps a COUNTIF test > 1 ?

Conditional Format formula of: =COUNTIF(range,criteria)>1

**scwi** · 05-28-2009, 10:10 PM

Hi lmsexcelforum

If you are using Excel 2007, then you can actually use the conditional formatting function.

Just go to home > conditional formatting, create a new rule while selecting your cells, select 'Format only unique or duplicate values' as the rule type, and you can choose to either format the duplicated ones or the unique one (duplicated for your case), and then choose the colours or fill according to your preference.

**lmsexcelforum** · 05-29-2009, 05:32 AM

This is what I was looking for - and just found it myself ...... was having difficultly updating the thread however - the page kept erroring when I tried to submit my reply.

Thanks.