Compare two worksheets and report differences into third worksheet

JedHass

New Member
Joined
Nov 2, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi

I need to compare two worksheets based on a unique identifier (column A). Based on the unique identifier, I need a method to easily compare each column between Sheet1 and Sheet2, and any differences reported into a seperate worksheet (Sheet3).

The best I have been able to do is create a simple macro which would compare Sheet1 and Sheet2 but this only HIGHLIGHTS the differences between the two sheets e.g.

Sheet1:
1635859179498.png


Sheet2:
1635859200785.png


This kind of helps but ideally I would like to be able to report the differences into a Sheet 3 as follows:
1635859360950.png


A few caveats:
- The IDs may not be in the same row position between the two sheets
- There may be some IDs in either sheet which don't exist in the other and I need to identify these too.

Below is the macro I currently have:

VBA Code:
Option Explicit

Sub DetectChanges()
    Dim ws1 As Worksheet, ws2 As Worksheet '<-- explicitly declare each variable type
    Dim ws1Data As Range, f As Range, cell As Range
    Dim icol As Long

    Set ws1Data = Worksheets("Sheet1").Columns(1).SpecialCells(xlCellTypeConstants) '<-- set a range with Sheet1 cells containing data

    With Worksheets("Sheet2") '<--| reference Sheet2
        For Each cell In Intersect(.UsedRange, .Columns(1)).SpecialCells(xlCellTypeConstants) '<-_| loop through its column "A" non blank cells
            Set f = ws1Data.Find(what:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole) '<--| search for current cell value in Sheet1 data
            If f Is Nothing Then '<--| if not found then...
                Intersect(cell.EntireRow, .UsedRange).Interior.ColorIndex = 3 '<--| highlight current cell entire row
            Else
                For icol = 1 To .Range(cell, .Cells(cell.Row, .Columns.Count).End(xlToLeft)).Columns.Count - 1 '<--| loop through Sheet2 current cell row
                    If f.Offset(, icol) <> cell.Offset(, icol) Then '<--| if it doesn't match corresponding cell in Sheet1
                        cell.Offset(, icol).Interior.ColorIndex = 3 '<--| highlight Sheet2 not-matching cell
                        f.Offset(, icol).Interior.ColorIndex = 3 '<--| highlight Sheet1 not-matching cell
                    End If
                Next icol
            End If
        Next cell
    End With
End Sub

I've tried searching and can't find anything like this that already exists, if anyone knows of an existing macro which would output in the way I would like, I would appreciate it.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,043
Messages
5,767,797
Members
425,436
Latest member
MSPaperclipMan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top