Data Import & Export

How to Compare Two Excel Contact Spreadsheets and Remove Duplicates

Maira Mehtab Oct 10, 2026 869 views

Question details

The user needs to compare two Excel contact workbooks with different row orders to find unique contacts and remove duplicates.

Compare Two Excel Contact Spreadsheets and Remove Duplicates

Product: Excel
Device & OS: not provided
Scenario: Merging or updating contact lists from two different workbooks without duplicating records.
Observed behavior: Contacts are spread across two workbooks in different row orders, requiring a formula-based comparison to identify matching or unique records before combining them into a final list.

Before you start

Ensure both spreadsheets have a unique identifier column, such as an email address or employee ID, which is essential for accurately matching records.

Solution 1Recommended

Use VLOOKUP to Compare and Filter Duplicates

Use the VLOOKUP function to identify which contacts exist in both sheets, filter the results, and remove duplicates before combining.

By creating a helper column, you can use VLOOKUP to search for a unique identifier across both workbooks. A successful match returns the data, while an #N/A error indicates a unique contact that doesn't exist in the second list.

Open Both Workbooks

Open both 'Spreadsheet A' and 'Spreadsheet B' in Excel so that both files are active.

Create a Match Column

In 'Spreadsheet A', insert a new column next to your contact data and name the header 'Match'.

Enter the VLOOKUP Formula

In the first data row of the Match column (e.g., Row 2), enter `=VLOOKUP(A2,'[Spreadsheet B.xlsx]Sheet1'!A:B,1,FALSE)`. Assume column A contains the unique identifier like an email address.

Fill Down the Formula

Select the cell with the formula and double-click the fill handle in the bottom-right corner to apply it to all rows in your list.

Filter and Deduplicate

Apply a filter to the Match column. Rows showing a name or email exist in both sheets (duplicates), while rows showing '#N/A' are unique to Spreadsheet A. Delete the matched rows, or copy only the unique rows and append them to Spreadsheet B.

Use VLOOKUP to Compare and Filter Duplicates

Understanding #N/A values: An #N/A result simply means the formula could not find the lookup value in the second spreadsheet, indicating that the record is unique to the current list.

Data Management

Easily Compare and Deduplicate Spreadsheets with WPS Office

WPS Spreadsheet offers powerful formula support, including VLOOKUP and MATCH, along with built-in duplicate removal tools to seamlessly merge and manage large contact lists.

1. Open Workbooks in Tabs: Launch WPS Office and open both contact spreadsheets. They will load in a unified, tabbed workspace for easy cross-referencing.
2. Use Highlight Duplicates: If you paste both lists into one sheet, select the Data tab and choose 'Highlight Duplicates' to quickly visualize matching records in color.
3. Apply Remove Duplicates: Navigate to the Data tab and click 'Remove Duplicates'. Select your unique identifier column to instantly clean up the merged contact list.
4. Save the Master List: Once duplicates are removed, save the unified file in standard Excel (.xlsx) format to retain compatibility.

Built-in 'Highlight Duplicates' and 'Remove Duplicates' tools for instant cleanupFull compatibility with Microsoft Excel formulas like VLOOKUP and MATCHSupport for opening multiple workbooks in a single tabbed window for easy switchingFree, lightweight, and user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP formula returning an error for data I know is in both sheets?

This often happens due to trailing spaces, formatting differences (like text vs. number), or incorrect range references. Ensure both columns are formatted identically and consider wrapping your cell references in the TRIM function to remove hidden spaces.

Can I use the MATCH function instead of VLOOKUP?

Yes. You can use a formula like `=MATCH(A2,'Spreadsheet B'!A:A,0)`. This will return the specific row number where the match was found instead of the cell value. It will still return an #N/A error if the contact is entirely unique.

Is there a way to remove duplicates without using complex formulas?

Yes. If you prefer not to use formulas, simply copy all contacts from Spreadsheet A and paste them at the bottom of Spreadsheet B. Then, select all the data, go to the Data tab, and click 'Remove Duplicates', making sure to select the unique identifier column (e.g., Email).

What should I use as a unique identifier for comparing contacts?

Email addresses, phone numbers, or employee ID numbers are the most reliable unique identifiers. Relying on First and Last Names can be problematic if two different people share the exact same name.

Excel & Data Problems