Compare Two Microsoft Excel Data Sets and Report Changed Values by ID
Question details
Create an efficient comparison of dated records that reports only the IDs and fields whose values changed.

- Product
- Microsoft Excel workbook with VBA macros
- Device & OS
- not provided
- Scenario
- The user has multiple records for the same ID on different dates and needs to compare each ID's consecutive records.
- Observed behavior
- The desired result is a separate change report that shows the ID, changed field, old value, new value, and relevant record dates.
Ensure the source table has a header row, a unique ID column, and a date column that can be sorted chronologically. Decide which columns are comparison fields, and save the workbook as a macro-enabled .xlsm file before running VBA code.
Use a VBA macro to sort records and create a field-level change report
Use this method when records are stored in one table and each ID can have multiple dated rows. The macro compares adjacent records after sorting by ID and date, then writes only differences to a new Changes worksheet.
This approach assumes column A contains the ID, column B contains the record date, and row 1 contains field names. It compares columns C through the last used column; adjust these column positions in the code if your layout is different.
Place all records on a worksheet named Data. Put ID values in column A, dates in column B, field headers in row 1, and the data beginning in row 2.
In Excel, press Alt+F11, select Insert > Module, and paste the macro below into the new code window: Sub ReportChanges() Dim ws As Worksheet, ch As Worksheet Dim lastRow As Long, lastCol As Long, r As Long, c As Long, outRow As Long Set ws = Worksheets("Data") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column ws.Sort.SortFields.Clear ws.Sort.SortFields.Add Key:=ws.Range("A2:A" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending ws.Sort.SortFields.Add Key:=ws.Range("B2:B" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending With ws.Sort .SetRange ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)) .Header = xlYes .Apply End With Application.DisplayAlerts = False On Error Resume Next Worksheets("Changes").Delete On Error GoTo 0 Application.DisplayAlerts = True Set ch = Worksheets.Add(After:=ws) ch.Name = "Changes" ch.Range("A1:F1").Value = Array("ID", "Field", "Old Value", "New Value", "Old Date", "New Date") outRow = 2 For r = 3 To lastRow If ws.Cells(r, 1).Value = ws.Cells(r - 1, 1).Value Then For c = 3 To lastCol If CStr(ws.Cells(r, c).Value) <> CStr(ws.Cells(r - 1, c).Value) Then ch.Cells(outRow, 1).Value = ws.Cells(r, 1).Value ch.Cells(outRow, 2).Value = ws.Cells(1, c).Value ch.Cells(outRow, 3).Value = ws.Cells(r - 1, c).Value ch.Cells(outRow, 4).Value = ws.Cells(r, c).Value ch.Cells(outRow, 5).Value = ws.Cells(r - 1, 2).Value ch.Cells(outRow, 6).Value = ws.Cells(r, 2).Value outRow = outRow + 1 End If Next c End If Next r ch.Columns.AutoFit End Sub
Return to Excel, press Alt+F8, select ReportChanges, and choose Run. The macro sorts the Data sheet by ID and date, then creates a worksheet named Changes.
On the Changes sheet, select any cell in the header row and choose Data > Filter. Filter the Field column to review a specific field, or filter the ID column to inspect one record's change history.

Create an ID-Based Change Report in WPS Spreadsheet
WPS Spreadsheet can help you organize dated records, use formulas or VBA-compatible macros where available, and generate a focused report of values that changed for each ID. Import your existing workbook, sort records by ID and date, and use the macro workflow to review field-level differences.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheet, and open the Excel .xlsx or .xlsm file containing the dated records.
- 2. Confirm the data columns: Verify that the source sheet has an ID column, a date column, and headers for every field that should be compared.
- 3. Sort records by ID and date: Select the data range, open Data > Sort, then sort first by ID and then by Date in ascending order.
- 4. Use the macro or formula comparison method: Open the Developer or Macro tools if available in your WPS Spreadsheet edition, add the comparison macro, and run it to create a Changes sheet.
- 5. Filter the generated change report: On the Changes worksheet, select Data > Filter and filter by ID or field name to focus on the updates you need to review.

Frequently Asked Questions
Can this macro compare two separate Excel worksheets instead of one dated table?
Yes. Use one worksheet as the older data set and one as the newer data set, then modify the macro to look up each ID in the other sheet before comparing corresponding fields. Both sheets should use the same ID column and matching field headers.
What happens when an ID exists on only one date?
The adjacent-row version of the macro reports changes only when the same ID appears in consecutive sorted rows. Add a check for missing IDs if you also need an Added or Removed status in the Changes sheet.
Why does the macro compare the wrong records for an ID?
Check that the Date column contains real Excel dates rather than text and that the sort runs first by ID and then by Date. Text dates may sort alphabetically instead of chronologically.
How do I exclude a column from the comparison?
Edit the For c = 3 To lastCol line in the VBA code, or add an If condition that skips the column number or header name you do not want to report.




