Power Query Problems

How to Compare Large Excel Files Using Two Identifiers

Maira Mehtab Sep 28, 2026 868 views

Question details

The user needs to match and audit large Excel spreadsheets by creating a composite key from two distinct identifiers (Drawing Number and Joint Number) in order to compare additional columns.

Product: Excel
Device & OS: not provided
Scenario: Auditing or matching massive datasets across multiple spreadsheets where a single column is insufficient to uniquely identify a row.
Observed behavior: The user wants to establish a reliable method to merge tables based on a multi-column key and accurately flag any discrepancies in the corresponding data.

Before you start

Before merging large datasets, ensure that the combination of your two identifier columns creates a truly unique key for every row to prevent unintended data duplication during the merge process.

Solution 1Recommended

Use Power Query to Create a Composite Key and Merge Tables

Power Query is the most efficient native tool for handling large Excel files, allowing you to combine multiple identifiers into a single key and merge tables without formula lag.

When dealing with large files, standard lookup formulas can drastically slow down your workbook. Power Query handles memory much more efficiently and provides a structured environment to flag discrepancies.

Import Data into Power Query

Open your Excel workbook, navigate to the Data tab on the ribbon, and select Get Data. Import both of your large tables into the Power Query Editor.

Create the Composite Key

In the Power Query Editor for your first table, hold the Ctrl key and select the Drawing Number and Joint Number columns. Right-click the column headers and choose Merge Columns to create a unified identifier. Repeat this exact process for your second table.

Merge the Queries

Navigate to the Home tab and click Merge Queries. Select your first table from the top dropdown and your second table from the bottom dropdown. Click on your newly created composite key columns in both preview windows to map them together, then click OK.

Expand and Compare Columns

Click the expand icon at the top of the newly merged column to reveal the data from the second table. Go to Add Column > Custom Column and write a conditional statement (e.g., if [Table1.Status] = [Table2.Status] then "Match" else "Mismatch") to flag the differences.

Load the Results

Once your comparison column is ready, click Close & Load on the Home tab to output the audited, matched dataset into a new worksheet.

Tip: It is highly recommended to test this process with a small sample of your data first. Verify that your custom column correctly flags differences before applying it to files with hundreds of thousands of rows.

WPS Spreadsheet Data Analysis

Compare Large Spreadsheets Easily in WPS Office

You can quickly and efficiently compare large datasets in WPS Spreadsheets by combining identifiers using built-in functions and leveraging powerful lookup tools to spot data differences without system lag.

1. Create a Helper Column: Open your files in WPS Spreadsheets and insert a new column at the beginning of both tables.
2. Generate the Composite Key: In the new column, use a formula like =A2&"-"&B2 (assuming A is Drawing Number and B is Joint Number) to combine the identifiers into a single string. Drag this down to apply it to all rows.
3. Pull Data for Comparison: In your primary table, use =XLOOKUP() or =VLOOKUP() to search for the composite key and return the specific data column from the secondary table that you want to check.
4. Highlight Differences: Select the original data column and the newly pulled data column. Go to Home > Conditional Formatting > Highlight Cell Rules to visually flag any values that do not match.

High performance engine for handling large Excel datasets smoothly.Fully compatible with Microsoft Excel formulas, including XLOOKUP and CONCATENATE.Intuitive conditional formatting tools to highlight data discrepancies instantly.

microsoft office alternative - wps office

Frequently Asked Questions

Can I merge tables based on more than two identifiers?

Yes. You can combine three or more columns into a single composite key. In Power Query, simply select all the relevant columns using the Ctrl key before selecting 'Merge Columns'. In standard spreadsheet formulas, you can chain multiple cells together using the '&' operator.

Why are my matched rows duplicating after merging data?

This occurs if your chosen identifiers do not create a perfectly unique key. If the same combination of Drawing Number and Joint Number appears multiple times in your secondary table, Power Query will create a new row for every match (a Cartesian product). You must clean your data to ensure uniqueness before merging.

Is it better to use Power Query or VLOOKUP for very large files?

For very large files (e.g., exceeding 100,000 rows), Power Query is significantly faster and more stable. Applying thousands of complex lookup and comparison formulas across large sheets can cause the application to freeze or crash, whereas Power Query handles the processing in the background.

How do I handle trailing spaces in my identifiers that prevent matching?

Before creating your composite key, you must sanitize your data. In Power Query, select your identifier columns, right-click, and navigate to Transform > Format > Trim. If using formulas, wrap your cell references in the TRIM() function to remove accidental spaces that cause exact matches to fail.

Excel & Data Problems