Compare data from one list to another if there are duplicates within the same column

[sbolt6]

Hello Everyone,

I would appreciate some assistance in trying to identify (highlight preferably) unique values in two columns but exclude values that are duplicates in a column. I did search the site but could only find a solution if there are no duplicates in the same column. Any help is much appreciated. Thanks in advance. A sample file is attached.

Scott

 

[Logit]

Here is one way. It locates the duplicates in both columns then highlights those dupes in both column. The remaining non-highlighted are unique values.

Option Explicit

Sub FindDupes()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim Rws As Long, Rng As Range, a As Range
Dim Rws2 As Long, rng2 As Range, c As Range

Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet1")


With sh1
Rws = .Cells(.Rows.Count, "D").End(xlUp).Row
Set Rng = .Range(.Cells(1, "D"), .Cells(Rws, "D"))
.Columns("D").Interior.Color = xlNone
End With
With sh2
Rws2 = .Cells(.Rows.Count, "M").End(xlUp).Row
Set rng2 = .Range(.Cells(1, "M"), .Cells(Rws, "M"))
End With


For Each a In Rng.Cells
For Each c In rng2.Cells
If a = c Then a.Interior.ColorIndex = 6
If a = c Then c.Interior.ColorIndex = 6
Next c
Next a

End Sub

 

[Chief Ace]

Here is another way. Conditional Formatting. I am not sure why there's an equal sign on the other column but i end up removing it. I change the first one entry just to show you it put color(s) if it found duplicate(s).

Regards
Chief Ace

 

[Peter Bartholomew]

I seem to have interpreted the requirement slightly differently. Using a defined Name 'Unique?'

Unique? :
= ( COUNTIFS(Table1[Journal Number],Table1[@[Journal Number]]) +
    COUNTIFS(Table2[Journal Number],Table1[@[Journal Number]]) )
    = 1

allows the formula to be developed first. and only then implemented as a conditional format.

Notes: 1. The white clear text is the conditional format and the greyed out is the underlying format.
2. I have used Tables to favour transparency over brevity.
3. Only one of the tables has been formatted.
4. Conditional formats like this are computationally intensive given that they are both volatile and are N-squared calculations.

 

[sbolt6]

Thank you all for your awesome responses. I appreciate you taking the time to help me out with your advice. Much appreciated!

 

[Logit]

You are welcome.