vba - Compare two sheets and find differences, copy to third sheet

Question

I have been trying to compare two sheets. The sheets are versions, one made in August, the other in September. In Sheet1, column C I have a unique ID that could also be in sheet 2, but could also be not present. On the other hand, I could have NEW ID's in sheet 2, that are not present in Sheet1.

I am trying to:

• Identify IDs not in "other" sheet, copy entire row to sheet3

• Check if C-column value exists in other sheet, then it has to find the differences in THOSE two rows, 12 columns out

Example, in sheet1:

ID          Jan Feb Mar Apr May
14578596    125 125 125 0   10
22345697      10 10 10 10 20 
12563654    150 150 75  75  75
85745896    890 890 890 890 790

and in sheet 2:

ID          Jan Feb Mar Apr May
14578596    125 125 125 0   10
12563654    150 150 75  75  75
85745896    890 890 790 890 790
87544545    0 0 0 0 10 

In sheet 3, it should copy over the newly added ID 87544545 and all the values in the following columns. It should copy over the ID's 22345697 entire row as well, as being non-existing in the other sheet is considered a difference.

For the others, that exist in both sheets, it should Take "Jan-Jan" and return the difference value. So it should lookup if "ID" exists in other sheet, if it does, compare the Jan-Feb-Mar with each other. Note that ID's are NOT in the same position in the sheets. With ID 85745896 it would return:

ID       Jan Feb Mar  Apr May
85745896 0   0   100  0   0

I have tried to look at topics such as Compare data from 2 sheets and find mismatches and Check if two rows are the EXACT SAME in MS Excel but can't seem to make them work for my challenge here.

Answer 1

Sub compare()
    For i = 1 To last_cell_mainSheet
        For j = 1 To last_cell_sheet2
        If Worksheets("main_sheet").Range("a" & i).Value = Worksheets("sheet2").Range("a" & j).Value 
        Then
           Worksheets("main_sheet").Range("C" & i).Value = Worksheets("sheet2").Range("b" & j).Value
        End If
        Next j
    Next i
End Sub