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
Last edited by lmsexcelforum; 05-29-2009 at 06:33 AM. Reason: to mark as solved
Perhaps a COUNTIF test > 1 ?
Conditional Format formula of: =COUNTIF(range,criteria)>1
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks