# Highlight Duplicates from Two Columns with Different Colours

1. ## 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.

2. ## Re: Highlight Duplicates from Two Columns with Different Colours

HI ddgacic,

Use the below formula in conditional formatting:-

Formula:

See attached..
Highlighting duplicates.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

3. ## 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. ## 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. ## 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:

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.
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.

6. ## 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

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

#### 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