VBA & Macro Problems

How to Compare and Update Excel Workbooks with Different Sheet Structures

Elise Williams Oct 19, 2026 868 views

Question details

User needs an efficient way to compare and update large Excel workbooks that possess different sheet structures without using slow VBA cell-by-cell loops.

How to Compare and Update Excel Workbooks with Different Sheet Structures

Product: Excel
Device & OS: not provided
Scenario: Comparing thousands of rows across two different workbooks to identify updates and overwrite destination data based on a unique numerical identifier.
Observed behavior: Nested 'For Each' loops reading cell-by-cell are extremely slow for 20,000 to 40,000 rows, and layout differences prevent standard positional matching.

Before you start

Ensure both Excel workbooks share at least one column with a unique numerical identifier (such as an ID number or SKU) to accurately match records across different layouts.

Solution 1Recommended

Use VBA Arrays and a Dictionary Object

The most efficient method for matching and updating tens of thousands of rows across structurally different workbooks.

Instead of looping through cells on the worksheet, loading your source and destination data into memory arrays significantly improves macro speed.

By utilizing a VBA Dictionary object, you can store the unique identifier as the 'Key' and the row's array index as the 'Item'. This allows the script to map corresponding rows instantly and write the updated data back to the sheet in a single operation.

Open the VBA Editor

Press ALT + F11 in Excel to open the Visual Basic for Applications (VBA) editor.

Create a New Module

Click 'Insert' > 'Module' and enable the 'Microsoft Scripting Runtime' reference via 'Tools' > 'References' to use the Dictionary object.

Load Data into Arrays

Write a script to assign the used ranges of both the source and destination worksheets to variant variables (arrays).

Populate the Dictionary

Loop through the source array, adding the unique numerical identifier column as the Key and the row data as the Item into the Dictionary.

Update the Destination Data

Loop through the destination array, check if its unique ID exists in the Dictionary, and update the array elements accordingly. Finally, write the array back to the destination worksheet in one step.

Performance Boost: This in-memory method takes mere seconds to execute, whereas traditional nested 'For Each' loops interacting directly with the worksheet can take several minutes or crash Excel.

Solution 2

Merge Datasets Using Power Query

A robust, no-code alternative that is highly efficient for merging and updating datasets based on unique columns.

Solution 3

Highlight Differences Using a Basic Cell-by-Cell Macro

Use this straightforward cell loop macro if you decide to standardize the sheet structures and only need to visually flag differences.

Advanced Data Handling

Efficiently Compare Workbooks in WPS Office

WPS Spreadsheet provides powerful tools, including advanced VBA macro support and built-in data handling features, making it incredibly simple to compare and update huge datasets across different files without lag.

1. Open Your Workbooks: Launch WPS Spreadsheet and open both your source and destination workbooks.
2. Access the Developer Tools: Navigate to the 'Developer' tab and click on 'VBA Editor' to manage your scripts.
3. Run Data Automation: Paste your Dictionary or array-based VBA macro and click 'Run' to seamlessly update mismatched rows.

Full compatibility with Microsoft Excel formats (.xlsx, .xlsm) and macrosSeamless execution of VBA Dictionary objects and array processingLightweight architecture handles large datasets and complex calculations easilyBuilt-in Data tools for quick filtering, merging, and highlighting duplicates

microsoft office alternative - wps office

Frequently Asked Questions

Why is a VBA cell-by-cell loop so slow for comparing large workbooks?

Reading and writing to individual cells continuously forces the macro to interact with the Excel application interface, causing massive performance drops. Loading data into memory using arrays requires only one interface read and one write, making it exponentially faster.

Can I use VLOOKUP or XLOOKUP instead of VBA to compare different sheet structures?

Yes, VLOOKUP or XLOOKUP can match unique identifiers across workbooks easily. However, running thousands of complex lookup formulas simultaneously can cause the workbook to become sluggish and increase file size dramatically compared to a one-time VBA or Power Query update.

Does Power Query require identical sheet layouts to work properly?

No. Power Query can merge tables based strictly on a chosen matching column (the unique identifier), completely ignoring the layout, order, or structure of the other columns in both workbooks.

What is 'Spreadsheet Compare' and can I use it for this?

Spreadsheet Compare is a Microsoft utility meant primarily for identifying structural and data differences between workbook versions. Because it lacks automated data-overwriting capabilities based on unique keys, it is not recommended for daily update processes.

Excel & Data Problems