Power Query Problems

Find Unique Names in Two Excel Columns with Power Query

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 870 views

Question details

Find names that appear only in the first column and names that appear only in the second column using Power Query.

Find Unique Names in Two Excel Columns with Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
Compare two columns of names and produce separate results for values exclusive to each column.
Observed behavior
The desired result is a list of names found only in each column, with duplicates removed if required.
Before you start

Place the names in an Excel table with headers and check for unwanted spaces or inconsistent capitalization. Power Query comparisons can treat differently capitalized or spaced names as different values.

Solution 1Recommended

Compare the Name Columns with Left Anti-Joins

Use the Power Query interface to create a separate result for names exclusive to each column.

1
Load the names

Select the Excel table and choose Data > From Table/Range to open it in Power Query Editor.

2
Create two queries

In the Queries pane, right-click the loaded query and select Duplicate. In each query, select the name column you want to keep and choose Home > Remove Columns > Remove Other Columns.

3
Find names only in the first column

Select the first-column query and choose Home > Merge Queries > Merge Queries as New. Select the second-column query, click the name column in each preview, and choose Left Anti as the join kind.

4
Reverse the comparison

Select the second-column query and repeat Home > Merge Queries > Merge Queries as New, this time selecting the first-column query as the second table and Left Anti as the join kind.

5
Clean and load the results

In each result, remove the added merge column. If each name should appear only once, select the name column and choose Home > Remove Rows > Remove Duplicates. Select Home > Close & Load to load the results into Excel.

Compare the Name Columns with Left Anti-Joins
Matching and duplicates: A left anti-join keeps rows from the first query that have no match in the second. It does not remove repeated unmatched names unless you apply Remove Duplicates.
Free Microsoft Office alternative

Compare Name Lists in WPS Spreadsheet

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and Excel format compatibility for easy migration. In WPS Spreadsheet, you can use COUNTIF helper formulas to find names missing from the other column; this is an alternative comparison method, not a Power Query fix.

Compare name columns with COUNTIF formulas and filter the results.Open and save Microsoft Excel XLSX workbooks.Work in a familiar interface that makes moving existing spreadsheets easier.Use a free, lightweight suite for everyday document and spreadsheet tasks.
microsoft office alternative - wps office

Frequently Asked Questions

What does a left anti-join return?

In Power Query's Merge Queries dialog, Left Anti returns rows from the first selected query that have no matching value in the second selected query.

How do I return names unique to the second column?

Create a second merge with the second-column query as the first table and the first-column query as the second table. Select Left Anti as the join kind.

Why do unmatched names appear more than once?

Anti-joins preserve repeated unmatched rows. Select the result's name column and choose Home > Remove Rows > Remove Duplicates, or use List.Distinct in an M script.

Can I compare the columns without Power Query?

Yes. For names in columns A and B, enter =IF(AND(A2<>"",COUNTIF($B:$B,A2)=0),A2,"") in a helper column and fill it down. Reverse the column references for names only in B, then filter the results and remove duplicates if needed. COUNTIF comparisons are generally case-insensitive.