Compare ranges in 2 worksheets for non-matches, then copy non-matches to 3rd worksheet

[TropicalMagic]

Hi all,

I would like to compare cells in Worksheet 2 against Worksheet 1.

First check for matching cells in range A for both Worksheets 1 and 2.

Next, if there are no matches, check for matching cells in range B for both Worksheets 1 and 2, otherwise continue to check the next cell in range A.

If there are no matches as well, copy these non-matching cells in both ranges A and B in Worksheet 2 to a new worksheet, Worksheet 3's range A1.


Here are my worksheets' layout:

Worksheet 1 -

Worksheet 2 -

Worksheet 3 -

From the screenshot of Worksheet 2, only the rows highlighted in yellow should be copied to Worksheet 3 since its values in both range A and B cannot be found in Worksheet 1's corresponding range A and B.


Here is my code so far, but it's only checking range A for both Worksheets 1 and 2:

```
Dim Cl As Range, Rng As Range, Dic As Object

Set Dic = CreateObject("scripting.dictionary")

With Dic
For Each Cl In wsk1.Range("A2", wsk1.Range("A" & Rows.Count).End(xlUp))
.Item(Cl.Value) = Empty
Next Cl
For Each Cl In wsk2.Range("A2", wsk2.Range("A" & Rows.Count).End(xlUp))
If Not .Exists(Cl.Value) Then
If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
End If
Next Cl
End With

If Not Rng Is Nothing Then
Rng.EntireRow.Copy wsk3.Range("A" & Rows.Count).End(xlUp)
End If
```

Many thanks!

 

[DanteAmor]

Try this:

Sub CompareRange()
  Dim dic As Object
  Dim c As Range
 
  Set dic = CreateObject("Scripting.Dictionary")
  For Each c In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(3))
    dic("A|" & c.Value) = Empty
    dic("B|" & c.Offset(, 1).Value) = Empty
  Next
  For Each c In Sheets("Sheet2").Range("A2", Sheets("Sheet2").Range("A" & Rows.Count).End(3))
    If Not dic.exists("A|" & c.Value) And Not dic.exists("B|" & c.Offset(, 1).Value) Then
      Sheets("Sheet3").Range("A" & Rows.Count).End(3)(2).Resize(1, 2).Value = Array(c.Value, c.Offset(, 1).Value)
    End If
  Next
End Sub

 

[TropicalMagic]

Fantastic, thanks! It worked as I intended!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.