+ Reply to Thread
Results 1 to 9 of 9

Compare values within a column (checking for duplicates)

  1. #1
    Registered User
    Join Date
    02-16-2006
    Posts
    49

    Compare values within a column (checking for duplicates)

    Hi,
    Thanks for all the help in the past.
    Here's today's question.
    We have a long list of items in column C and they should all be unique numbers, but sometimes we make mistakes and have a duplicate number there. Is there a formula that will constantly monitor column C and flag with conditional formatting (or anyway possible) any item which is the duplicate of another in that same column?
    Example:
    Column C
    Row 2: ES12-0111-A1
    Row 3: ES12-3514-A1
    Row 4: ES18-0111-A1
    Row 5: ES12-0116-A1
    Row 6: ES12-0111-A1
    Row 7: ES12-2397-A1
    Row 2 and Row 6 are the same and both need to be flagged in some way.
    It would be ideal if all items in column C were colored black except any ones that have a duplicate and those are colored red. If there is more than one pair of duplicates, it's OK if they're all colored red, we can sort that out on our own. (There won't be a lot of duplicates.)
    Thanks!

  2. #2
    Forum Contributor
    Join Date
    02-12-2008
    Location
    Doha-Qatar
    MS-Off Ver
    Excel 2007
    Posts
    137
    Hi,

    I think you will get a better answer than the one i suggest, but for the time being you may use Conditional formating if you use excel 2007.

    Go to conditional formating, click New Rule,choose format only unique or dublicate values, in the edit the rule description choose duplicate then format, then fill and choose your preferred color,

    this will give the color you chossed to duplicated values

    Regards

  3. #3
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Try conditional formatting with a formula like

    =COUNTIF($C$2:$C$7,C2)>1
    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578
    Using a modification of VBA Noob's formula, you can set it up in Data Validation (under Data menu) using Custom
    Please Login or Register  to view this content.
    where $C$2:$C$20 is your range and C2 is your first highlighted cell.

    Does this work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Forum Contributor
    Join Date
    02-12-2008
    Location
    Doha-Qatar
    MS-Off Ver
    Excel 2007
    Posts
    137
    Hi pdgood

    Please also check the following link, it has an answer for your inquiry provided by NBVC

    http://www.excelforum.com/excel-gene...uplicates.html

    Regards

  6. #6
    Registered User
    Join Date
    09-04-2008
    Location
    Sydney, Australia
    Posts
    9
    You could also use conditional formatting with Excel 2003, Select Conditional formatting and change to formula. Use this formula and then copy down to as many cells as you want to be checking. Adjust the formula if more or less than 1000 lines are required.

    Conditional Formula "=COUNTIF($A$1:$A$1000,A1)>1"

  7. #7
    Registered User
    Join Date
    02-16-2006
    Posts
    49
    Thanks I'll try some of these out.

  8. #8
    Registered User
    Join Date
    02-16-2006
    Posts
    49
    Results:
    Having some struggles here. Apparently Data validation doesn't work if the numbers are copy and pasted in - which is how they are likely to be entered - so this is not a good option.
    Conditional formatting seems like the answer, but I may be doing something wrong because I'm getting either no results or strange ones.
    Here's some more information.
    I'm on Excel 2004 for Mac.
    I selected all of column C before going to Format/Conditional. I assume this is what you're supposed to do.
    I tried entering the formula =COUNTIF($C$2:$C$1000,C2)>1 and chose red text.
    I typed in text in column C and also copied text that I knew was an exact duplicate of existing info in another cell but got no results. I went back and checked the formula and was surprised to see that C2 had changed itself to C15. This happened on several other attempts with varying numbers replacing C2.
    I also tried: =COUNTIF($C$2:$C$20,C2)=1 with similar results - however, occasionally a number would become red even though it had no duplicate.
    I also tried: =COUNTIF($A$1:$A$1000,A1)>1 and changed all the A's to Cs. This seems to be the same as the first formula.
    The strange behavior leads me to believe there is program corruption, except that it has worked flawlessly for me in all other ways for quite some time. I never have any trouble out of Excel and my computer is well behaved as well. Any other ideas?

  9. #9
    Registered User
    Join Date
    02-16-2006
    Posts
    49
    Here's what I've learned:
    The formula =COUNTIF(C2:C1000,C2)>1 works... but only for cell C2. This explains why it wasn't working when applied to the entire column. Also, it means that the formula needs to be put into a cell in another column - as opposed to putting it in the conditional formatting area. That might work but it would require a separate conditional format for every cell which is an imposing task.
    It returns TRUE if more than one copy exists (itself) and FALSE otherwise and it's easy enough to apply conditional formatting to anything that says TRUE - so we have a solution.
    Now...here's the tricky part...since the formula only works in one cell, it must be copied to all the other cells (hundreds). If I drag so that the cell number C2 increases with each row, then it also increases the numbers in the range (C2:C1000). These need to stay constant. I have tried every method I can think of to make this not happen. Is there any way to copy the formula into the rest of the column (C3 through C1000) changing only the cell number and not the range?
    Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Matching values in one column with values in another
    By Prium in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-26-2008, 09:53 PM
  2. Count specific text values contigent on value in next column
    By rgarber1950 in forum Excel General
    Replies: 2
    Last Post: 05-21-2008, 11:23 PM
  3. Compare cell values base on other columns
    By anyweather in forum Excel General
    Replies: 3
    Last Post: 04-08-2008, 06:32 PM
  4. Sum of one column based upon values in another
    By Creto in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-07-2008, 03:55 PM
  5. How to add column values using a macro
    By greaseman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-13-2007, 11:01 AM

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