Previous Topic Next Topic

2 sheets compare for differences

Closed
shawnash - Apr 21, 2011 at 01:12 PM
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 - Apr 28, 2011 at 04:42 AM
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

Answer 1 / 2
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Apr 22, 2011 at 05:33 AM 
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
1
Answer 2 / 2
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Apr 28, 2011 at 04:42 AM
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.
0