Macro to match two sets of data

Question

I need help to write macro to compare two sheets in excel, sheet1 and sheet2 on same excel book and display all the differences between both the sheets on sheet3. My headings will always be the same between sheet1 and sheet2 but the information in both the sheets may vary. I included the headings that will be on both the sheets:

ID Number Date of Birth Payroll Number Surname First Name Salary Member Group

I am struggling with this. So please extend your helping hands Thank you.

We are here to help you solve your issues. Rather then completely answering and writing code for you. Please let us us know what efforts have you put to do this. — Nehal
Try using match or countif functions. You don't need VBA for this. Or you can buy a plugin that does this for you. (More reading: Google) — vacip

Arun Thomas Arun Thomas · Accepted Answer · 2016-07-27T11:31:55

Assuming 'i' is the number of rows in Sheet1 and 'm' is the number of rows in Sheet2 and 7 headings in each sheets, then this is the solution where the mismatches are registered in Sheet3

Here the Id numbers in Sheet1 is compared with Id numbers in Sheet2 and if present, then they are compared and the mismatches are reported in sheet3 in the same order of the Headings

Sub Mismatch()

Dim temp3 As Integer
temp3 = 1

Dim array1(7), array2(7), array3(7) As Variant

For i = 2 To 6
Worksheets("Sheet1").Activate
For temp = 1 To 7
    array1(temp) = Cells(i, temp).Value
Next temp

Worksheets("Sheet2").Activate

For m = 2 To 6
    If Cells(m, 1).Value = array1(1) Then
        For n = 1 To 7
            For temp2 = 1 To 7
                array2(temp2) = Cells(m, temp2).Value
            Next temp2
            Worksheets("Sheet3").Activate
            temp3 = temp3 + 1

            Cells(temp3, 1).Value = array1(1)

            For temp4 = 2 To 7
                If array1(temp4) <> array2(temp4) Then
                    Cells(temp3, temp4).Value = "mismatch"
                End If
            Next temp4

            GoTo JumpToHere

        Next n
    End If
Next m
JumpToHere:
Next i

End Sub

I Hope this helps

Macro to match two sets of data

1 Answers