Function Problems

How to Remove Duplicates from Two Excel Columns and Exclude Matching Values

Maira Mehtab Sep 25, 2026 869 views

Question details

The user wants to generate unique lists from two separate columns and ensure that any values present in the first column are excluded from the second column's unique list.

How to Remove Duplicates from Two Excel Columns and Exclude Matching Values

Product: Excel
Device & OS: not provided
Scenario: Data cleaning and deduplication across multiple dataset columns.
Observed behavior: Needs to create distinct lists without overlapping values between the two specified columns.

Before you start

Ensure your spreadsheet software is updated to a version that supports dynamic array functions such as UNIQUE, FILTER, and XMATCH before proceeding.

Solution 1Recommended

Use Dynamic Array Formulas to Extract Unique Values

Utilize the UNIQUE, FILTER, and XMATCH functions to automatically generate distinct lists and exclude cross-column duplicates.

This method leverages dynamic array functions to automatically spill the results into adjacent cells, ensuring your extracted lists update instantly if the source data changes.

Extract unique values from the first column

Select an empty cell (e.g., D2) and enter =UNIQUE(A2:A151) to get a list of all unique values from your first data column.

Extract and filter unique values from the second column

Select another empty cell (e.g., E2) and enter =UNIQUE(FILTER(B2:B151,ISERROR(XMATCH(B2:B151,A2:A151)))). This checks column B against column A, excludes any matches, and returns only the remaining unique values.

Press Enter to spill the arrays

Hit Enter after typing the formulas. The distinct, non-overlapping lists will automatically spill down the respective columns.

Use Dynamic Array Formulas to Extract Unique Values

Automatic Updates: Because these are dynamic array formulas, your results will update automatically whenever new data is added or removed from columns A and B.

WPS Spreadsheet Solution

Easily Filter Data and Remove Duplicates with WPS Spreadsheet

WPS Spreadsheet fully supports dynamic array functions like UNIQUE, FILTER, and XMATCH. You can process complex datasets, eliminate duplicates, and extract unique values quickly without paying for expensive software.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the two columns you want to deduplicate.
2. Apply the UNIQUE formula: Click on a blank cell and type the =UNIQUE() formula to instantly extract non-repeating values from your primary column.
3. Combine FILTER and XMATCH: Use =UNIQUE(FILTER()) alongside the XMATCH function in the next column to effortlessly exclude values that already appeared in the first column.

Seamless compatibility with Microsoft Excel formulas and functionsFull support for advanced dynamic array formulas like UNIQUE and FILTERIntuitive UI that makes data analysis and deduplication effortlessLightweight application that processes large datasets without lag

microsoft office alternative - wps office

Frequently Asked Questions

Why is the UNIQUE or FILTER formula returning a #NAME? error?

The #NAME? error typically occurs if your spreadsheet software version does not support dynamic array functions. Upgrading to the latest version of WPS Office or Excel will resolve this issue.

Can I use this method to compare more than two columns?

Yes, but the formula becomes more complex. You would need to nest additional XMATCH or COUNTIF functions within the FILTER criteria to exclude matches from multiple preceding columns.

Is there a non-formula way to remove duplicates from two columns?

Yes. You can copy both columns into a single column, use the 'Remove Duplicates' feature found under the Data tab, and then separate the data manually if needed. However, this is a static method and will not update automatically if the original data changes.

Excel & Data Problems