Compare sheet 1 & Sheet 2 pull any items that

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I saw this posted earlier... did you ever respond to this?

+++
Your macro has been a great help to identify the matching records- thank you. I have another situation in which I'm trying to compare the same data between sheet 1 and sheet 2, but I want sheet 3 to pull any items are are NOT matches. How can I change this macro to copy the non-matches to sheet 3?

I would like to compare a certain # of characters in sheet 1 and sheet 2 (for example - compare first 5 charaters in sheet1 to sheet 2 if no mathces found - populate on sheet 3.

Thanks

Greg



1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
quick reply see whether this works. if not post a very small extract of sheet1. 2 and what you expect in sheet 3

Sub test()
Dim rng As Range, c As Range, cfind As Range
On Error Resume Next
Worksheets("sheet3").Cells.Clear
With Worksheets("sheet1")
Set rng = Range(.Range("A2"), .Range("a2").End(xlDown))
For Each c In rng
With Worksheets("sheet2")
Set cfind = .Columns("A:A").Cells.Find _
    (what:=c.Value, lookat:=xlWhole)
If NOT cfind Is Nothing Then GoTo line1
cfind.Copy Worksheets("sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End With 'sheet 2
line1:
Next c
application.cutcopymode=false 
End With 'sheet 1


End Sub