Compare sheets and put missing rows into third sheet. [Closed]

Registration date
Friday January 12, 2018
Last seen
January 13, 2018
Hi to all the CCM members.

I have a problem that I need assistance with and I would greatly appreciate it if a VB guru could help me with a script to compare two spreadsheets and place the rows found in sheet1 and not found in sheet2 into sheet3

Sheet1 contains a list of:

Name ID Status
Brian 1 In
Tess 2 In
Shay 3 In
Peter 4 In
Gary 5 In

Sheet2 contains similar list but rows are missing

Name ID Status
Brian 1 In
Shay 3 In
Peter 4 In

Sheet three needs to display the Heading and only the missing rows

I would be forever grateful.



See more 

1 reply

Registration date
Thursday July 24, 2014
Last seen
January 10, 2019
Thank you
Hello Brian,

Try the following code in a standard module and assigned to a button:-

Sub Diffs()

    Dim lr As Integer
    Dim fValue As Range
    Dim c As Range
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

Sheet3.Rows(1).Value = Sheet1.Rows(1).Value
Sheet1.Range("A2:C" & lr).Copy Sheet1.[BA2]

For Each c In Sheet1.Range("A2:A" & lr)
    Set fValue = Sheet2.Columns("A:A").Find(c.Value)
        If fValue Is Nothing Then GoTo Nextc
            If c.Value = fValue.Value Then
                Range(c, c.Offset(0, 2)).ClearContents
          End If
Next c

For Each c In Sheet1.Range("A2:A" & lr)
    If c.Value <> "" Then
         Sheet1.Range(Cells(c.Row, "A"), Cells(c.Row, "C")).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
         End If
    Next c
Sheet1.Range("BA2:BAC" & lr).Copy Sheet1.[A2]
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

The code does as you would like. Codes for this sort of task can become quite involved so I've taken a little short cut and used some helper columns further over to the right (and out of the way) of Sheet1.

Following is the link to a little sample I've prepared for you. Click on the "RUN" button to see it work.

I hope that this helps.