+ Reply to Thread
Results 1 to 6 of 6

Highlight Duplicates from Two Columns with Different Colours

  1. #1
    Registered User
    Join Date
    03-29-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    27

    Highlight Duplicates from Two Columns with Different Colours

    I have two columns A1 (Section X) and B1 (Section Y)

    Each column has 20 rows of data (A2-B21) such as:
    X Y
    1 33
    2 34
    3 35
    4 36
    5 37
    3 35
    7 39
    8 40
    9 41
    10 42
    11 43
    12 44
    10 42
    14 46
    10 42
    16 48
    17 49
    18 50
    19 51
    20 52

    What I need is either dynamic formula or macros that can do the following:

    1) Assess range A2-B21;

    2) Identify sets of duplicates based on the identical values in both columns - for example in this case there are two sets of duplicates:

    -Set 1: A4 (4)/B4(36) is identical with A7/(4)B7(36); and

    -Set 2: A11(10)/B11(42) is identical with: A14(10/B14(42) is identical with A16(10)|B16 (42)

    3) Highlight cells that contain sets of duplicates with different colors – for example:

    Cell color for Set (A4 /B4 & A7/B7) should be different to cell color for Set 2 (A11/B11, A14/B14 & A16|B16).

    This coloring should be dynamic – data in this table is updated on adaily basis so as the number of duplicates will vary all the time.

    Thanks in advance

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Highlight Duplicates from Two Columns with Different Colours

    HI ddgacic,

    Use the below formula in conditional formatting:-

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See attached..
    Highlighting duplicates.xlsx

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    03-29-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    27

    Re: Highlight Duplicates from Two Columns with Different Colours

    Hi Dilipandey

    This formula highlights all sets of duplicates but with the same color. What I needed was something that can select different color for each set of duplicates as outlined in my original thread. For example, Cell color for Set (A4 /B4 & A7/B7) should be different to cell color for Set 2 (A11/B11, A14/B14 & A16|B16).

    Regards
    David

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Highlight Duplicates from Two Columns with Different Colours

    Hi David,

    I am not sure how to tell Excel to differentiate between different set of duplicates... There could be ways but idea is not clicking in my mind currently

    making myself an interested party here and will look forward to any good idea that may come up from excel experts....

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Highlight Duplicates from Two Columns with Different Colours

    based on the sample you provided, the examples you quote make no sense, because they do not match?

    upload a sample workbook please, showing before and after, and how you arrived at that
    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    03-29-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    27

    Re: Highlight Duplicates from Two Columns with Different Colours

    My apologies for some errors.
    The right list is provided below along with the attachment.

    X Y
    1 33
    2 34
    4 36
    3 35
    5 37
    4 36
    7 39
    8 40
    9 41
    10 42
    11 43
    12 44
    10 42
    14 46
    10 42
    16 48
    17 49
    18 50
    19 51
    20 52
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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