Compare two worksheets create new worksheet with differences

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Compare two worksheets create new worksheet with differences

Post by 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.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Compare two worksheets create new worksheet with differences

Post by HansV »

Recent versions of Office have a Spreadsheet Compare tool:

S1482.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Compare two worksheets create new worksheet with differences

Post by 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
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Compare two worksheets create new worksheet with differences

Post by 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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Compare two worksheets create new worksheet with differences

Post by 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.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Compare two worksheets create new worksheet with differences

Post by 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
Last edited by YasserKhalil on 03 Jun 2022, 06:54, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Compare two worksheets create new worksheet with differences

Post by HansV »

The code does not create a new sheet.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Compare two worksheets create new worksheet with differences

Post by YasserKhalil »

HansV wrote:
03 Jun 2022, 06:52
The code does not create a new sheet.
I have added the part of adding the worksheet and results.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Compare two worksheets create new worksheet with differences

Post by 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
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Compare two worksheets create new worksheet with differences

Post by YasserKhalil »

Amazing. Thank you very much for your great support.

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: Compare two worksheets create new worksheet with differences

Post by 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
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Compare two worksheets create new worksheet with differences

Post by YasserKhalil »

Thank you very much. Very clever trick.