+ Reply to Thread
Results 1 to 11 of 11

Highlighting duplicates

  1. #1
    Registered User
    Join Date
    07-11-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    58

    Highlighting duplicates

    Hi guys, I know this question has probably been asked 1000 times before, but not of the solutions I have seen seem to work for me.

    I have a table with several columns and rows. Column E contains the reference number for each row. Essentially, E2 to E1500 has a load of reference numbers.

    To the side, not part of this table, I then have a general list of reference numbers with no formatting on it at all. This list ranges from N4 to N600. What I need to do is check every reference number in my table to the list on the side, and if it matches anything in the list for it to be highlighted. So if anything from E2 to E1500 matches anything in N4 to N600, highlight it.

    Is this possible with a formula or macro?


    Thanks

  2. #2
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Highlighting duplicates

    If you want to avoid a formula to compare the data, it'd be easiest to just download an excel add-in like kutools that provides this functionality with a couple quick clicks.

    http://www.extendoffice.com/product/...d-columns.html

  3. #3
    Registered User
    Join Date
    07-11-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    58

    Re: Highlighting duplicates

    I did actually try kutools, but whenever I clicked on anything it gave me a kind of warning popup with no information on it whatsoever. So not to sure if its working for me.

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Highlighting duplicates

    Highlight column, go to Conditional Formatting and select Highlight cells rules and choose Duplicate values
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,678

    Re: Highlighting duplicates

    This is simply done with conditional formatting. Applied to E2:E1500 use this formula as a conditional formatting rule:

    =COUNTIF($N$4:$N$600,E2)>0
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  6. #6
    Registered User
    Join Date
    07-11-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    58

    Re: Highlighting duplicates

    demo.xlsx
    The problem I have with highlight cell rules, it only shows duplicates in column E. It seems to ignore comparing them against my other list.

    And with the formula =COUNTIF($N$4:$N$600,E2)>0 , I place this in say K2 (which is an empty column) and then I drag it all the way down to K1500. Every single value displayed in K is false though.

    I have attached an example to demonstrate.

    Thanks

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,678

    Re: Highlighting duplicates

    The formula I provided is intended to be a CF rule, not a cell formula. However, maybe your demo is just to test out the formula. In your demo file, none of the values in column A are found in column F. So of course it always returns FALSE.

    Your original post didn't mention needing to parse through multiple values in the same cell, which makes this a lot more complicated.

  8. #8
    Registered User
    Join Date
    07-11-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    58

    Re: Highlighting duplicates

    Hi, even as a CF rule, nothing is highlighted. I am slightly confused when you say that none of the values in column A are found in column F. For instance, F2 is found in A260, so this is a match. If I do need to parse through multiple values in the same cell, what would be my options?

    Cheers

  9. #9
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,678

    Re: Highlighting duplicates

    The string in F2 has a space at the end. The string in A260 does not. Therefore no match. If you do a find & replace on your entire sheet to eliminate all spaces, you will get 286 matches.

    Parsing through multiple values in the same cell means either:
    • Additional "helper" columns that break out the values. The formula would be a little complicated because sometimes the separator is "," and sometimes it's "/" and sometimes it's both.
    • VBA code to do the whole thing.

    I am better at VBA than I am at complicated formulas so that's where I would lean. Others might be better ideas.

  10. #10
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: Highlighting duplicates

    Check this ( I hope I have understand..)

    I think Nothing is matched in the two lists..

    So I intentionally produce a duplicate and CF has worked..
    Attached Files Attached Files
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  11. #11
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Highlighting duplicates

    There are a couple of problems with your data. Column A does not have spaces following the strings while the other column does have spaces following. There are duplicated values in column A and the other column at the same time. I have used 3 colours to identify duplicates of the various kinds.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ 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. Replies: 6
    Last Post: 02-16-2013, 07:29 AM
  2. Highlighting every other set of duplicates
    By IUgrad04 in forum Excel General
    Replies: 3
    Last Post: 12-27-2011, 08:08 AM
  3. highlighting duplicates
    By alex in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-01-2006, 01:45 PM
  4. [SOLVED] highlighting duplicates
    By Lynn in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-11-2005, 11:05 AM
  5. [SOLVED] Help with Highlighting all duplicates in a row
    By Jimv in forum Excel General
    Replies: 4
    Last Post: 04-21-2005, 03:06 PM

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