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

TropicalMagic

New Member
Joined
Jun 19, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
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 -

1629810064269.png


Worksheet 2 -

1629810069710.png


Worksheet 3 -

1629810075728.png



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!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Try this:

VBA Code:
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
 
Upvote 0
Solution
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top