Compare 2 excel worksheet, consolidate differences in 3rd sheet

[aldusarugay]

Hi All,

I would like some help regarding my code. I want a function that compares 2 excel sheets in a workbook and consolidate their differences in the 3rd sheet. Right now my code can compare the 2 sheets and highlight their differences. All i want in the 3rd sheet is to show what are the changes, e.g (added data, deleted data, changed data). Thanks in advance for helping me :D Here is my code :

Dim xlApp As New Excel.Application
        Dim xlPreviousWorkBook, xlCurrentWorkbook, xlOutputWorkBook As Excel.Workbook
        Dim xlPreviousWorkSheet, xlCurrentWorksheet, xlPreviousOutputWorksheet, xlCurrentOutputWorksheet, xlResultWorksheet As Excel.Worksheet
        Dim xlPreviousRange, xlCurrentRange, xlPreviousDestRange, xlCurrentDestRange, xlResultRange As Excel.Range
        Dim lastline As String
 
 
 
        ' Opens Previous Workbook. Change path and filename as applicable
        xlPreviousWorkBook = xlApp.Workbooks.Open("C:\Users\Desktop\Previous Version\cola")
 
        'Opens Cuurent Workbook.  Change path and filename as applicable
        xlCurrentWorkbook = xlApp.Workbooks.Open("C:\Users\Desktop\Current Version\cola2")
 
        ' Opens Destination Workbook. Change path and filename as applicable
        xlOutputWorkBook = xlApp.Workbooks.Open("C:\Users\Desktop\Output File Location\Differences")
 
        ' Display Excel
        xlApp.Visible = True
 
        ' Set the previous worksheet
        xlPreviousWorkSheet = xlPreviousWorkBook.Sheets("Sheet1")
        ' Set the current worksheet
        xlCurrentWorksheet = xlCurrentWorkbook.Sheets("Sheet1")
        ' Set the destination worksheet
        xlPreviousOutputWorksheet = xlOutputWorkBook.Sheets("Sheet1")
        xlCurrentOutputWorksheet = xlOutputWorkBook.Sheets("Sheet2")
        xlResultWorksheet = xlOutputWorkBook.Sheets("Sheet3")
 
        ' Set the previous range
        xlPreviousRange = xlPreviousWorkSheet.UsedRange
        ' Set the current range
        xlCurrentRange = xlCurrentWorksheet.UsedRange
        ' Set the previous destination range
        xlPreviousDestRange = xlPreviousOutputWorksheet.UsedRange
        ' Set the current destination range
        xlCurrentDestRange = xlCurrentOutputWorksheet.UsedRange
        ' Set the result destination range
        xlResultRange = xlResultWorksheet.UsedRange
 
        ' Copy and paste the range
        xlPreviousRange.Copy(xlPreviousDestRange)
        xlCurrentRange.Copy(xlCurrentDestRange)
 
        ' Compare sheet1 and sheet2 but only highlights the difference between the two sheets
        Try
            lastline = Math.Max(xlPreviousOutputWorksheet.UsedRange.Rows.Count, xlCurrentOutputWorksheet.UsedRange.Rows.Count)
 
            For row As Integer = 1 To lastline
                Dim lastcol As Integer = Math.Max(xlPreviousOutputWorksheet.UsedRange.Rows.Count, xlCurrentOutputWorksheet.UsedRange.Rows.Count)
                For column As Integer = 1 To lastcol
 
                    ' Highlights the difference on both sheets
                    If xlPreviousOutputWorksheet.Cells(row, column).Value <> xlCurrentOutputWorksheet.Cells(row, column).Value Then
 
                        DirectCast(xlPreviousOutputWorksheet.Cells(row, column), Excel.Range).Interior.Color = 5296274
                        DirectCast(xlCurrentOutputWorksheet.Cells(row, column), Excel.Range).Interior.Color = 5296274
 
                    End If
 
                Next
            Next
 
 ' Save and close excel apps
            xlOutputWorkBook.Save()
            xlOutputWorkBook.Close()
            xlPreviousWorkBook.Close()
            xlCurrentWorkbook.Close()
            xlApp.Quit()
 
            MessageBox.Show("Finished Processing")
 
        Catch ex As Exception
            MsgBox("ERROR")
        End Try
    End Sub
 
 

[Luk3r]

When your program highlights a line, you should also just add that line to a list of strings and then loop through the list of strings and write each string to your new workbook.

[aldusarugay]

good day Luk3r. i will try to do what you are suggesting. thank you. i'll get back to you if there any progress on my code.

[aldusarugay]

hi again actually what im trying to do is to consolidate the difference in the 3rd sheet.

for example:

in the cells 3rd sheet it shows,

Added - present in sheet 2 but not in sheet 1

Deleted - present in sheet 1 but not in sheet 2

Changed - there is value on same cell in both sheet but the values are different