Function Problems

How to Compare, Sort, and Separate Matching Values in Excel

Guest Writer Sep 25, 2026 870 views

Question details

The user needs to compare two columns of alphanumeric values to identify matches, sort the results, and separate the duplicate and unique values.

How to Compare, Sort, and Separate Matching Excel Values

Product: Excel
Device & OS: not provided
Scenario: Comparing two lists of data to find matching and unique entries for data organization and extraction.
Observed behavior: Identify matching and unique entries, sort the dataset based on the first column, and extract specific values based on whether they are duplicates or unique.

Before you start

Ensure your data is organized into clear columns without blank rows, and that both columns you intend to compare are formatted identically (e.g., both formatted as Text or General) to avoid false mismatches.

Solution 1Recommended

Use the MATCH Formula and Data Filtering

This method uses a combination of an IF and MATCH formula to identify duplicates, followed by standard sorting and filtering tools to separate the data.

Apply the Comparison Formula

Assuming your values are in columns A and B, click on cell C1 and enter the formula `=IF(ISNUMBER(MATCH(A1,B:B,0)),"Match","No Match")`. Press Enter, then double-click or drag the fill handle down to apply it to all rows in your dataset.

Sort the Data

Select your entire dataset (Columns A, B, and C). Navigate to the 'Data' tab on the top ribbon, click 'Sort', and choose to sort by Column A to organize your results alphabetically or numerically.

Filter and Separate

Highlight your column headers, go to the 'Data' tab, and click 'Filter'. Click the filter drop-down on Column C and select 'Match' to view and copy duplicate values, or 'No Match' to view and extract unique values to a new sheet.

Use the MATCH Formula and Data Filtering

Using Absolute References: If your data in Column B is within a specific finite range, you can use absolute references like $B$1:$B$100 instead of B:B. This improves calculation speed on very large spreadsheets.

Efficient Data Comparison in WPS

Easily Compare and Filter Lists with WPS Spreadsheet

WPS Spreadsheet provides powerful functions and intuitive filtering tools to compare columns, find duplicates, and manage large datasets efficiently without lag.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the columns you want to compare.
2. Apply the comparison formula: In an empty adjacent column (e.g., C1), type `=IF(ISNUMBER(MATCH(A1,B:B,0)),"Match","No Match")` and drag the fill handle down the column.
3. Filter the results: Navigate to the 'Data' tab, select 'AutoFilter', and use the drop-down menu on your new column to isolate 'Match' or 'No Match' values.

Seamless compatibility with Microsoft Excel formulas like IF and MATCH.Intuitive Sorting and AutoFilter interface for quick data separation.Built-in 'Highlight Duplicates' feature for visual data comparison without writing formulas.Lightweight, fast, and completely free to use.

microsoft office alternative - wps office

Frequently Asked Questions

How can I visually highlight matching values instead of separating them?

You can use Conditional Formatting. Select the two columns you want to compare, navigate to the Home tab, click 'Conditional Formatting', choose 'Highlight Cells Rules', and then select 'Duplicate Values' to automatically color-code matching entries.

Does the MATCH function differentiate between uppercase and lowercase text?

No, the standard MATCH function is case-insensitive. If you need a case-sensitive comparison between your columns, you will need to use a combination of the EXACT and MATCH functions configured as an array formula.

Why is my formula returning 'No Match' for values that look exactly the same?

This usually happens due to formatting inconsistencies, such as one column being formatted as Text and the other as Numbers, or there might be hidden trailing spaces in one of the cells. Try using the TRIM() or VALUE() functions to clean your data before comparing.

Excel & Data Problems