Previous Topic Next Topic

2 sheets compare for differences

Closed
shawnash - 21 Apr 2011 à 13:12
RWomanizer Posts 365 Registration date Monday 7 February 2011 Status Contributor Last seen 30 September 2013 - 28 Apr 2011 à 04:42
Hello,

I have excel workbook with sheet1, sheet2 that have same data in column A. I need to find the difference in the 2 sheets. The records that are in sheet 1 might not be in sheet2, so I would like to have a macro that will put anything that is in sheet1, but not in sheet2 into a new sheet3. Is it possible to do this comparing column A of both sheets?

Thanks in advance, Shawnash
Related:

2 responses

Réponse 1 / 2
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
22 Apr 2011 à 05:33 
Sub test()
Dim r As Range, c As Range, cfind As Range, x
With Worksheets("sheet1")
Set r = Range(.Range("A2"), .Range("A2").End(xlDown))
For Each c In r
x = c.Value
MsgBox x
With Worksheets("sheet2").Columns("A:A")
Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
If cfind Is Nothing Then
GoTo nextstep
Else
GoTo nextc
End If
End With
nextstep:
c.EntireRow.Copy
With Worksheets("sheet3")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
nextc:
Next c
End With
End Sub
Réponse 2 / 2
RWomanizer Posts 365 Registration date Monday 7 February 2011 Status Contributor Last seen 30 September 2013 120
28 Apr 2011 à 04:42
You can do this simply by using VLOOKUP.

insert a column next to column A and put the formula in that as:

=VLOOKUP(A:A,Sheet2!A:A,1,FALSE),

now filter use filter in sheet 1
look for #N/A.
Copy all the rows and paste them into sheet 3.

hope this will solve your problem.