Share via

Excel: How to explicitly find duplicates between two columns without finding duplicates within same columns?

Anonymous
2022-07-29T17:22:26+00:00

Hi,

I've been trying to figure out how I can find duplicate values between 2 columns. Whenever I use the Conditional Rules, it finds and highlights duplicate values between 2 columns, but also highlights duplicate values within same columns.

This makes it unreliable.

Is there a way I can explicitly find values only between two columns without having to check for values in same column?

Example:

Column A Column B

Jon Susan

Susan Dylan

Jon Bob

Bob

Louise

The desired result should be that the only duplicate values between Column A and Column B are Susan and Bob.

But using the default rule in Excel it also identifies Jon as duplicate within Column A. This we don't want as it becomes unreliable for us.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2022-07-29T23:49:54+00:00

    Hi,

    In cell E2, enter this formula

    =UNIQUE(FILTER(A2:A6,COUNTIF(C2:C4,A2:A6)>0))

    Hope this helps.

    20+ people found this answer helpful.
    0 comments
  2. HansV 462.6K Reputation points MVP Volunteer Moderator
    2022-07-29T19:46:18+00:00

    Let's say the data begin in row 2.

    Select the cells in column A in which you want to highlight matches with column B.

    The active cell should be A2.

    On the Home tab of the ribbon, select Conditional Formatting > New Rule...

    Select 'Use a formula to determine which cells to format'.

    Enter the formula

    =MATCH(A2,$B$2:$B$100,0)

    (You only need to adjust the range if the data in column B extend below B100.)

    Click Format...

    Activate the Fill tab.

    Select a color.

    Click OK, then click OK again.

    Do the same for column B, but with the formula

    =MATCH(B2,$A$2:$A$100,0)

    10+ people found this answer helpful.
    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  5. Anonymous
    2022-07-29T23:49:39+00:00

    Re: find duplicates between columns

    The "List Common Items" utility in the free "Professional Compare" workbook

    can do that...

    Result example...

    The result is placed in the first blank area to the right of the data.

    Note: shading common items shades all duplicate instances.

    Download from OneDrive... https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

    '---

    Nothing Left to Lose

    0 comments