Compare sheets and put missing rows into third sheet.

Biffzs 2 Posts Friday January 12, 2018Registration date January 13, 2018 Last seen - Jan 13, 2018 at 10:26 AM - Latest reply: vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen
- Jan 15, 2018 at 06:46 AM
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.

Thanks

Brian




See more 

Your reply

1 reply

vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Jan 15, 2018 at 06:46 AM
0
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.UsedRange.ClearContents
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
Nextc:
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.

http://ge.tt/4rQCO7o2

I hope that this helps.

Cheerio,
vcoolio.
Respond to vcoolio