+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    01-22-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Detect a duplicate value in a column / range

    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 05:33 AM. Reason: to mark as solved

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Detect a duplicate value in a column / range

    Perhaps a COUNTIF test > 1 ?

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

  3. #3
    Registered User
    Join Date
    05-28-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    4

    Re: Detect a duplicate value in a column / range

    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.

  4. #4
    Registered User
    Join Date
    01-22-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Detect a duplicate value in a column / range

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1