How to Compare Two Excel Sheets Using Macro

Comparing two Excel spreadsheets manually can be tedious and error-prone, especially with large datasets. Leveraging the power of VBA macros allows for automated comparison, highlighting differences efficiently. This article delves into how to create a macro that compares two Excel sheets and logs discrepancies in a separate summary file.

Automating Excel Sheet Comparison with VBA

This macro aims to compare two specified columns within two separate Excel files (“File1” – the old file and “File2” – the new file). Any differences found will be recorded in a third file (“SummaryResults” – the archive file). This process is particularly useful for tasks like tracking price or quantity changes in inventory lists, ensuring data accuracy and facilitating efficient updates.

VBA Code for Comparing Excel Sheets

The following VBA code provides a foundation for comparing two Excel sheets:

Sub CompareExcelFiles()

    Dim File1 As Workbook
    Dim File2 As Workbook
    Dim SummaryResults As Workbook
    Dim Sheet1 As Worksheet
    Dim Sheet2 As Worksheet
    Dim SummarySheet As Worksheet
    Dim i As Long
    Dim lastRow As Long


    ' Open the Excel files
    Set File1 = Workbooks.Open("C:PathToFile1.xlsx") ' Replace with actual path
    Set File2 = Workbooks.Open("C:PathToFile2.xlsx") ' Replace with actual path
    Set SummaryResults = Workbooks.Open("C:PathToSummaryResults.xlsx") ' Replace with actual path


    ' Set the worksheets to compare
    Set Sheet1 = File1.Worksheets("Sheet1") ' Replace "Sheet1" with the actual sheet name
    Set Sheet2 = File2.Worksheets("Sheet1") ' Replace "Sheet1" with the actual sheet name
    Set SummarySheet = SummaryResults.Worksheets("Sheet1") ' Replace "Sheet1" with the actual sheet name


     'Find the last row with data in Sheet1
    lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row


    ' Loop through each row and compare specific columns (e.g., Column A and B)
    For i = 2 To lastRow ' Assuming data starts from row 2
        If Sheet1.Cells(i, 1).Value <> Sheet2.Cells(i, 1).Value Or _
           Sheet1.Cells(i, 2).Value <> Sheet2.Cells(i, 2).Value Then


           ' Copy the entire row from Sheet1 to the Summary sheet if a difference is found.
           Sheet1.Rows(i).EntireRow.Copy Destination:=SummarySheet.Cells(SummarySheet.Rows.Count, 1).End(xlUp).Offset(1, 0)


        End If
    Next i


    ' Close the workbooks without saving changes to the original files
    File1.Close SaveChanges:=False
    File2.Close SaveChanges:=False
    SummaryResults.Close SaveChanges:=True


    ' Clean up object variables
    Set File1 = Nothing
    Set File2 = Nothing
    Set SummaryResults = Nothing
    Set Sheet1 = Nothing
    Set Sheet2 = Nothing
    Set SummarySheet = Nothing


    MsgBox "Comparison complete. Differences logged in SummaryResults.xlsx."


End Sub

Implementing the Macro

Before running this macro, ensure that:

1. File Paths: Correct the file paths in the code to point to your actual file locations.
2. Sheet Names: Verify that the sheet names (“Sheet1” in this example) match your target sheets in both File1 and File2.
3. Column Selection: Adjust column indices within the If statement (e.g. Cells(i,1), Cells(i,2)) to reflect the specific columns you want to compare.
4. Data Starting Row: Modify the starting point of the loop For i = 2 To lastRow if your data begins on a row other than row 2.

This macro provides a robust solution for automating Excel sheet comparisons, saving time and enhancing accuracy in data analysis. Remember to customize it based on your specific needs.