How to Compare Two Excel Sheets and Find the Latest Version
Question details
The user needs to compare records across two Excel worksheets using a shared key and a version column (like column Z) to identify and extract the most recent version of each record.
- Product
- Microsoft Excel
- Device & OS
- Mac and Windows
- Scenario
- Comparing matching records between two different sheets to find or extract the newest version based on a designated version identifier.
- Observed behavior
- Excel for Windows includes a built-in Compare Workbooks feature, but Excel for Mac lacks this functionality, requiring alternative methods like VBA, Power Query, or formulas to achieve the comparison.
Before running any VBA macros or applying complex Power Queries, ensure you have saved a backup copy of your original workbooks to prevent accidental data loss during the comparison process.
Use VBA to Compare Sheets and Extract the Latest Version
Since Excel for Mac lacks the built-in Compare Workbooks feature, using a VBA macro is the most robust cross-platform method to highlight differences and copy the newest records into a consolidated sheet.
VBA allows you to automate the comparison of columns A through K based on a version number in column Z. This method works on both Windows and Mac versions of Excel that support macros.
Press Alt + F11 (or Option + F11 on a Mac) to open the Visual Basic for Applications (VBA) editor.
Click on 'Insert' in the top menu and select 'Module' to create a new blank script window.
Write or paste a VBA script designed to loop through the rows of both sheets, compare the shared keys, evaluate the version numbers in column Z, and highlight the differences or copy the highest version to a new sheet.
Close the VBA editor, return to your Excel workbook, press Alt + F8 (or Option + F8), select your macro, and click 'Run'.
Use Excel Formulas with XLOOKUP or INDEX/MATCH
For users who prefer not to use macros, formulas can effectively compare version numbers between two sheets by pulling data into a helper column.
Merge Queries Using Power Query
Power Query allows you to join two tables based on a shared key and easily filter or sort to keep the latest version. This is highly effective for large datasets.
Easily Compare Worksheets with WPS Office
WPS Spreadsheet provides powerful data processing capabilities, including advanced formulas and macro support, allowing you to easily compare records across multiple sheets and find the latest versions without complex setups.
- 1. Open Your Workbooks: Launch WPS Office and open both the old and new versions of your spreadsheet.
- 2. Set Up a Comparison Column: In your primary sheet, add a new column next to your data to pull in the version numbers from the second sheet.
- 3. Apply Lookup Formulas: Use the VLOOKUP or XLOOKUP function to match the unique record IDs and retrieve the version values from the second sheet.
- 4. Filter for the Latest Version: Use a simple IF formula to compare the two version columns, then apply a Filter to display and extract only the rows with the latest version.
Frequently Asked Questions
Why doesn't Excel for Mac have the Compare Workbooks feature?
Microsoft has not included the Spreadsheet Compare add-in or the legacy Compare Workbooks feature in the macOS version of Excel. Mac users must rely on VBA macros, Power Query, or third-party tools to compare datasets.
Can I compare two sheets without using VBA?
Yes, you can use formulas like XLOOKUP, VLOOKUP, or INDEX/MATCH to pull the version number from the second sheet into the first sheet and compare them using an IF function. Power Query is also a great non-VBA alternative.
Does Power Query work on Excel for Mac for comparing sheets?
Recent updates to Excel for Mac have introduced Power Query support, allowing you to get data from local workbooks, merge them based on a unique ID, and filter for the highest version number, though some advanced features may still be Windows-only.
