Compare 2 table with varying data to show differences

[kvmike]

Hi,

I pull a report weekly for my role which contains multiple columns of data, each row has its own unique ID.
Each week when i pull the report, currently i am manually comparing the 2 tables to see if there are any differences and copying any changes from the new report into the old table. Also adding any new entries that come through in the new report into the old table.
I was wondering if there is a faster way of doing this?
My main objectives are to highlight any differences between the old report and the new report for each unique ID.
Here is an example table below to give you an idea:

Old Report:

ID	Name	Status	Total Contract Value	Estimated Contract Received Date
779513	Ethernet bulk	Quoting	£ 600	30/06/2019​
1046170	MPLS replacement	Signed Contract	£ 110,000	25/08/2021​
1069535	AP bulk	Quoting	£ 14,647	28/09/2021​

New Report:

ID	Name	Status	Total Contract Value	Estimated Contract Received Date
779513	Ethernet bulk	Signed Contract	£ 1000	30/06/2019​
1046170	MPLS replacement	Signed Contract	£ 110,000	25/08/2021​
1069535	AP bulk (went with another provider)	Lost	£ 14,647	28/09/2021​

Any help is greatly appreciated!

 

[Zot]

Questions that helpers out there might want to ask:
1) Are New Report and Old Report in the same workbook?
2) How would you like the resulting Old Report to be like since you mentioned highlight difference and also copy any changes.?

 

[kvmike]

Yes, the new and old are in the same workbook.
My goal is for:

• The old report to cross check each unique ID, and its corresponding cells against the same ID in the new report.
• If the ID's corresponding cells in the old report differ to those in the new report, then I want it to highlight those cells so it brings them to my attention. I can then manually investigate what's changed since I last ran my report.
• If there is no difference found when checking the above, i want it to do nothing and leave the data in the cell being crosschecked alone.

Unsure if this is at all possible, or if there is a much simpler method to doing this? thank you

 

[Zot]

Sorry for late reply. I was hoping that someone respond before me ?

I created a macro to take each ID in old report and compare to new report. If matching ID is found, then each data in column is compared and highlight cell in old report if corresponding cell is different. The macro is self explanatory.

Sub Compare()

Dim nCol As Long
Dim IDOld As Range, IDNew As Range
Dim rngIDOld As Range, rngIDNew As Range, rngFound As Range
Dim wsOld As Worksheet, wsNew As Worksheet

' Assign names for Report Old and Report New
Set wsOld = ActiveWorkbook.Sheets("Report Old")
Set wsNew = ActiveWorkbook.Sheets("Report New")

' Declare ID ranges for Report Old and Report New
Set rngIDOld = wsOld.Range("A2", wsOld.Cells(Rows.Count, "A").End(xlUp))
Set rngIDNew = wsNew.Range("A2", wsNew.Cells(Rows.Count, "A").End(xlUp))

' Clear any previous highlight. Assuming your data range is from A2 to E & end of data row
wsOld.Range("A2", wsOld.Cells(Rows.Count, "E").End(xlUp)).Interior.Color = xlNone

For Each IDOld In rngIDOld
    Set rngFound = rngIDNew.Find(IDOld)
    If Not rngFound Is Nothing Then
        For nCol = 2 To 5
            If Not wsNew.Cells(rngFound.Row, nCol) = wsOld.Cells(IDOld.Row, nCol) Then
                wsOld.Cells(IDOld.Row, nCol).Interior.Color = vbYellow
            End If
        Next
    End If
Next

End Sub