Compare two worksheets create new worksheet with differences

[YasserKhalil]

Hello everyone

I have two worksheets with the same number of rows and columns (the rows are above 100,000 rows and the columns are 300 columns)
What is the best approach to compare these two worksheets and create a new sheet with the differences?

Example of difference: Sheet1 > A3: Hans & Sheet2 > A3: Yasser
In the report, I need to spot on the different values like that
Column A -------- Column B -------- Column C ------
A3 ---------------- Hans -------------- Yasser

And the values in column B and column C to have a hyperlink to the target address, so as to go easily and modify according to my suits.

[HansV]

Recent versions of Office have a Spreadsheet Compare tool:

S1482.png

[YasserKhalil]

Thank you very much for guiding me to such a tool.
It is very close to what I exactly need, but I need to focus on the values only away from the formatting. And the most important is to create a link for both different values so as to be easily to navigate to the desired cells and modify according to some conditions on my side.

[YasserKhalil]

I have figured out how to spot on the differences with the following code
How can I create a hyperlink for the results (the values in column B and column C)

Code: Select all

Sub Compare_Two_Worksheets()
    Dim a, b, i As Long, ii As Long, k As Long
    a = shCA.Range("A1").CurrentRegion.Value
    b = shAC.Range("A1").CurrentRegion.Value
    ReDim c(1 To UBound(a, 1) * UBound(a, 2), 1 To 3)
    For i = LBound(a, 1) To UBound(a, 1)
        For ii = LBound(a, 2) To UBound(a, 2)
            If a(i, ii) <> b(i, ii) Then
                k = k + 1
                c(k, 1) = shCA.Cells(i, ii).Address
                c(k, 2) = a(i, ii)
                c(k, 3) = b(i, ii)
            End If
        Next ii
    Next i
    If k > 0 Then
        Stop
    End If
End Sub

[HansV]

Your macro produces an array, not a range.
An array does not contain hyperlinks.

I hope you are aware that with 100,000 rows and 300 columns, the array c will have 3*30,000,000 = 90,000,000 items.

[YasserKhalil]

The arrays are OK and very fast. I have tested it and I could get the results I need exactly. It is just the point of creating the hyperlinks to the values in the new created worksheet.

Code: Select all

Sub Compare_Two_Worksheets()
    Const sSheetName As String = "Report"
    Dim a, b, i As Long, ii As Long, k As Long
    a = shCA.Range("A1").CurrentRegion.Value
    b = shAC.Range("A1").CurrentRegion.Value
    ReDim c(1 To UBound(a, 1) * UBound(a, 2), 1 To 3)
    For i = LBound(a, 1) To UBound(a, 1)
        For ii = LBound(a, 2) To UBound(a, 2)
            If a(i, ii) <> b(i, ii) Then
                k = k + 1
                c(k, 1) = shCA.Cells(i, ii).Address(0, 0)
                c(k, 2) = a(i, ii)
                c(k, 3) = b(i, ii)
            End If
        Next ii
    Next i
    If k > 0 Then
        On Error Resume Next
            Application.DisplayAlerts = False
                ThisWorkbook.Worksheets(sSheetName).Delete
            Application.DisplayAlerts = True
        On Error GoTo 0
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = sSheetName
        With ThisWorkbook.Worksheets(sSheetName)
            .DisplayRightToLeft = True
            .Range("A1").Resize(, 3).Value = Array("Address", "Old Sheet", "New Sheet")
            .Range("A2").Resize(k, UBound(c, 2)).Value = c
            .Columns.AutoFit
        End With
    End If
End Sub

[HansV]

The code does not create a new sheet.

[YasserKhalil]

The code does not create a new sheet.

I have added the part of adding the worksheet and results.

[HansV]

I haven't tested it (obviously), but try adding the following lines above End With:

Code: Select all

            For i = 1 To k
                .Hyperlinks.Add Anchor:=.Cells(i + 1, 2), Address:="", SubAddress:="'" & shCA.Name & "'!" & c(i, 1)
                .Hyperlinks.Add Anchor:=.Cells(i + 1, 3), Address:="", SubAddress:="'" & shAC.Name & "'!" & c(i, 1)
            Next i

[YasserKhalil]

Amazing. Thank you very much for your great support.

[SpeakEasy]

Alternatively you could figure out the destination on the fly in response to, say, a doubleclick on Report sheet by adding an event macro to the workbook, e.g

Code: Select all

  ' You may want some additional simple checks in here to ensure clicked cell on Report is within the legitimate table
 ' Variety of ways of doing this
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim targetcell As String
    If Sh.Name = "Report" Then
        targetcell = Range("A" & Target.Row).Value
        Select Case Target.Column
            Case 2
                Application.Goto shCA.Range(targetcell) 
            Case 3
                Application.Goto shAC.Range(targetcell) 
            Case Else
        End Select
    End If
End Sub

[YasserKhalil]

Thank you very much. Very clever trick.