Compare and insert missing Row Values Problem

[steelstorm]

I have 2 worksheets in the same workbook (sheet1, sheet2). What I need is help with the code to look at sheet1 and add any values to sheet2 that are not present.

Now the thing that is giving me the issue is I guess I need to concatenate? Or something along those lines. I need the fruit column and the numbers to be looked at together, not individually.

So from my example attached the following values should be placed into sheet2 since they are not present. I am looking for the VBA code to do this btw.

Apple 3
Pear x
Pinapple 1
Kiwi 1
Banana 6
Nectarine 0


Thanks in advance for any help or advice.

[Trebor76]

Hi steelstorm,

Welcome to VBA Express!!

See how this goes:

Option Explicit
Sub Macro1()
 
    'http://www.vbaexpress.com/forum/showthread.php?t=42039
 
    Dim lngMyRow As Long, _
        lngMyCol As Long
    Dim lngLookUpCol As Long
    Dim strLookUpCol As String, _
        strMyFormula As String
    Dim wstSource As Worksheet, _
        wstCompare As Worksheet
    Dim rngCell As Range
        
    Set wstSource = Worksheets("Sheet1")
    Set wstCompare = Worksheets("Sheet2")
    
    Application.ScreenUpdating = False
    
    'Create a primary key in the first unused column of the 'wstSource' tab.
    With wstSource
        lngMyCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
        lngMyRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        With .Range(.Cells(2, lngMyCol), .Cells(lngMyRow, lngMyCol))
            .Formula = "=A2&B2"
            .Value = .Value
        End With
    End With
    
    'Create a primary key in the first unused column of the 'wstCompare' tab.
    With wstCompare
        lngMyCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
        lngMyRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        With .Range(.Cells(2, lngMyCol), .Cells(lngMyRow, lngMyCol))
            .Formula = "=A2&B2"
            .Value = .Value
        End With
    End With
    
    'Copy any data (Col's A & B) from the 'wstSource' tab to the 'wstCompare' tab where the 'wstSource' primary key does not exist on the 'wstCompare' tab.
    With wstSource
        lngMyCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        lngMyRow = .Cells(Rows.Count, lngMyCol).End(xlUp).Row
        lngLookUpCol = wstCompare.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        strLookUpCol = Left(Cells(1, lngLookUpCol).Address(True, False), Application.WorksheetFunction.Search("$", Cells(1, lngLookUpCol).Address(True, False)) - 1)
        For Each rngCell In .Range(.Cells(2, lngMyCol), .Cells(lngMyRow, lngMyCol))
            If IsError(Evaluate("VLOOKUP(" & rngCell.Address & "," & CStr(wstCompare.Name) & "!" & strLookUpCol & ":" & strLookUpCol & ",1,FALSE)")) = True Then
                .Range("A" & rngCell.Row & ":B" & rngCell.Row).Copy _
                    Destination:=wstCompare.Cells(Rows.Count, "A").End(xlUp)(2)
            End If
        Next rngCell
    End With
    
    'Delete primary key columns
    With wstSource
        lngMyCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        .Columns(lngMyCol).Delete
    End With
    
    With wstCompare
        lngMyCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        .Columns(lngMyCol).Delete
    End With
    
    Application.ScreenUpdating = False
    
    MsgBox "Any missing data has been copied."
 
End Sub

HTH,

Robert

[steelstorm]

That works perfect thank you for the help. Now it's time to study your code a little and understand this more

I originally had this written to just look at data from one column, but for some reason when the second column of data got put into the mix I just could not wrap my head around the concept for some reason.

Thanks again!

[Trebor76]

Thanks for the feedback. I'm glad we were able to help