Comparing source sheet and Dest sheet , and copying the unmatched data in the source sheet

Question

I have two Sheets Sht1 and Sht2.

I am comparing the column A of sheet1 with column A of sheet2. The column A of both the Sheets, contains ID.

If there is a non matching ID in sheet2, then I want to copy the unmatched row in sheet1.

I tried a code below, and the problem is, it is just copying the unmatched last row of sheet2 multiple times and keeps running without Exit.

Could anyone help me how i could correct it.

Sub trialtest()
    Dim srcLastRow As Long, destLastRow As Long
    Dim srcWS As Worksheet, destWS As Worksheet
    Dim i As Long, j As Long
    Application.ScreenUpdating = False
    Set srcWS = ThisWorkbook.Sheets("S2")
    Set destWS = ThisWorkbook.Sheets("S1")
    srcLastRow = srcWS.Cells(srcWS.Rows.Count, "A").End(xlUp).Row
    destLastRow = destWS.Cells(destWS.Rows.Count, "A").End(xlUp).Row
    For i = 5 To destLastRow
        For j = 5 To srcLastRow
            If destWS.Cells(i, "A").Value <> srcWS.Cells(j, "A").Value Then
                 destWS.Cells(i, "A") = srcWS.Cells(j, "A")
                 destWS.Cells(i, "B") = srcWS.Cells(j, "B")
                 destWS.Cells(i, "C") = srcWS.Cells(j, "C")
                 destWS.Cells(i, "D") = srcWS.Cells(j, "D")
                 destWS.Cells(i, "E") = srcWS.Cells(j, "E")
                 destWS.Cells(i, "F") = srcWS.Cells(j, "F")
                 destWS.Cells(i, "G") = srcWS.Cells(j, "G")
                 destWS.Cells(i, "H") = srcWS.Cells(j, "H")
                 destWS.Cells(i, "I") = srcWS.Cells(j, "I")
                 destWS.Cells(i, "J") = srcWS.Cells(j, "J")
                 destWS.Cells(i, "K") = srcWS.Cells(j, "K")
                 destWS.Cells(i, "L") = srcWS.Cells(j, "L")
                 destWS.Cells(i, "M") = srcWS.Cells(j, "M")
                 destWS.Cells(i, "N") = srcWS.Cells(j, "N")
                 destWS.Cells(i, "O") = srcWS.Cells(j, "O")
                 destWS.Cells(i, "P") = srcWS.Cells(j, "P")
                 destWS.Cells(i, "Q") = srcWS.Cells(j, "Q")
                 destWS.Cells(i, "R") = srcWS.Cells(j, "R")
                 destWS.Cells(i, "S") = srcWS.Cells(j, "S")
             End If
         Next j
    Next i

    Application.ScreenUpdating = True
End Sub

Answer 1

Try this code

Sub trialtest()
    Dim srcLastRow As Long, destLastRow As Long, rowIndex As Long
    Dim srcWS As Worksheet, destWS As Worksheet
    Dim i As Long, j As Long
    Dim found As Boolean

    Application.ScreenUpdating = False

    Set srcWS = ThisWorkbook.Sheets("S2")
    Set destWS = ThisWorkbook.Sheets("S1")
    srcLastRow = srcWS.Cells(srcWS.Rows.Count, "A").End(xlUp).Row
    destLastRow = destWS.Cells(destWS.Rows.Count, "A").End(xlUp).Row
    rowIndex = destLastRow
    found = False
    For i = 5 To srcLastRow
        For j = 5 To destLastRow
            'Debug.Print srcWS.Cells(i, "A").Value & " : " & destWS.Cells(j, "A").Value
            If srcWS.Cells(i, "A").Value = destWS.Cells(j, "A").Value Then
                found = True
                'rowIndex = rowIndex + 1
                'destWS.Cells(rowIndex, "A") = srcWS.Cells(j, "A")
                Exit For
            End If
        Next j
        If found = False Then
            rowIndex = rowIndex + 1
            'destWS.Cells(rowIndex, "A") = srcWS.Cells(i, "A")
            destWS.Range("A" & rowIndex & ":S" & rowIndex).Value = srcWS.Range("A" & i & ":S" & i).Value
        End If
        found = False
    Next i

    Application.ScreenUpdating = True
End Sub

Let me know if anything is not clear.

Answer 2

I know you have accepted an answer however, i just want to share this approach with you:

If i understood your question correctly, if an ID in sheet 1 is not equal to an ID in sheet 2, then replace that sheet 1 ID with the ID from sheet 2?

Option Explicit
Dim i, n As Long

Sub IDReplace()

n = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

With Sheets("Sheet1")
    For i = 2 To n
        If .Cells(i, 1).Value <> .Parent.Sheets("Sheet2").Cells(i, 1).Value Then
            .Cells(i, 1).Value = .Parent.Sheets("Sheet2").Cells(i, 1).Value
        End If
    Next i
End With


End Sub

Based on the fact that Sheet 1 is the main sheet you are focusing on, you need only count the rows of Sheet 1 and not Sheet2

Happy to Help :)

Answer 3

I would work here with the find method. with the find method you can look if the ID from Sheet S2 is in Sheet S1.

if it finds the ID in Sheet S1 the Variable c has the ID Value. If it don't find the ID in Sheet S1, the value of c is Nothing. Then the code will copy the Row at the End your List of ID's from Sheet S1.

Sub trialtest()
Dim srcLastRow As Long, destLastRow As Long
Dim srcWS As Worksheet, destWS As Worksheet
Dim i As Long, j As Long
Application.ScreenUpdating = False
Set srcWS = ThisWorkbook.Sheets("S2")
Set destWS = ThisWorkbook.Sheets("S1")
srcLastRow = srcWS.Cells(srcWS.Rows.Count, "A").End(xlUp).Row
destLastRow = destWS.Cells(destWS.Rows.Count, "A").End(xlUp).Row



With destWS.Range(Cells(5, 1), Cells(destLastRow, 1))
    For j = 5 To srcLastRow

        Set c = .Find(srcWS.Cells(j, "A").Value, LookIn:=xlValues)
        ' if value not in destWS copy it form srcWS
        If c Is Nothing Then
            srcWS.Range("A" & j & ":S" & j).Copy _
            Destination:=destWS.Cells(destLastRow + 1, 1)
            destLastRow = destLastRow + 1
        End If

    Next j
End With

Application.ScreenUpdating = True
End Sub