Compare sheets and put missing rows into third sheet.
Closed
                    
        
                    Biffzs
    
        
                    Posts
            
                
            1
                
                            Registration date
            Friday January 12, 2018
                            Status
            Member
                            Last seen
            January 13, 2018
            
                -
                            Updated on Jan 15, 2018 at 06:53 AM
                        
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jan 15, 2018 at 06:46 AM
        vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jan 15, 2018 at 06:46 AM
        Related:         
- Compare sheets and put missing rows into third sheet.
 - Sheet right to left google sheets - Guide
 - Windows network commands cheat sheet - Guide
 - Little alchemy cheat sheet - Guide
 - "Compare versions word" - Guide
 - Beyond compare download - Download - File management
 
1 response
                
        
                    vcoolio
    
        
                    Posts
            
                
            1411
                
                            Registration date
            Thursday July 24, 2014
                            Status
            Moderator
                            Last seen
            September  6, 2024
            
            
                    262
    
    
                    
Jan 15, 2018 at 06:46 AM
    Jan 15, 2018 at 06:46 AM
                        
                    Hello Brian,
Try the following code in a standard module and assigned to a button:-
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.
            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.