A common task in Excel is to compare two lists to identify which items are duplicates, different or missing from one list compared to the other.
We can use Power Query* to easily compare two lists like these staff lists below:
For referencing purposes in this tutorial I’ve formatted the names that are different in each list above in red.
Tip: I recommend you format your lists in an Excel Table before loading into Power Query.
*Power Query is available in desktop versions of Excel 2010 and 2013 as a free add-in which you can download here. In Excel 2016 it’s available on the Data tab in the Get & Transform group.
Power Query for Excel 2010 and 2013 Office 365 is only available with Office 365 ProPlus and Enterprise licences.
Excel Compare Two Lists with Power Query
Step 1: Load the tables into Power Query.
Select any cell in Table1 > go to the Power Query tab (Excel 2016 Data tab) > From Table.
This opens the Power Query editor window. Go to the Home tab > Close & Load To (as shown below):
In the Load To dialog box select ‘Only Create Connection’ > Load:
Now you should have one query listed in your Workbook Queries pane:
Repeat for Table2 so you have two queries in your Workbook Queries pane:
Step 2: Merge the Queries
Now we need to merge the queries. Right-click on one of the queries in the Workbook Queries pane > Merge:
This will open the Merge dialog box. Table1 should already be listed (because it was the one you right-clicked), so you just need to select Table2 as the second table you want to merge.
Then select the columns in each table that you want to match. The selected columns will be highlighted in green fill. If you have more than one column you want matched then hold down CTRL and select them in order so that the column numbers, which are automatically inserted, correspond to one another as you can see below.
Lastly you need to choose the join kind. There are 6 different join kinds but we’re only interested in the last 3.
- Inner – will return only those rows that are present in both tables i.e. the rows that match or duplicate.
- Left Anti – will return rows that are present in the first table but not in the second table.
- Right Anti – will return rows that are present in the second table but not in the first table.
Let’s take a look at each one in turn.
Note: if you don’t have a drop down list for Join Kind you will need to upgrade your Power Query add-in to the latest version.
Inner Join Kind
Continuing on from above, select Inner in the Join Kind list > click ok in the Merge dialog box. The Query Editor opens and you can see the results of Table1 but the results for Table2 are in ‘NewColumn’:
Click on the double arrow beside NewColumn to expand it. Uncheck ‘Use original column name as prefix’ > Click OK.
Now you can see the results of both tables (image below) but notice there are only 17 names because 4 names (2 in each table) have been omitted since they didn’t match.
So we can see the Inner join returns a table of only matching, or duplicate rows and removes any rows that don’t match.
Left Anti Join Kind
Next repeat the process except this time choose ‘Left Anti’ in the join kind list. This will return rows that only appear in the first table and not the second table as you can see in the Query Editor below:
Expanding NewColumn will return nulls since those names aren’t in the second table:
You can select the last two columns and press DELETE to get rid of them.
Right Anti Join Kind
Lastly repeat the process and choose Right Anti in the join kind list. Right Anti returns rows that appear only in the second table, not the first.
The results in the Query Editor are a bit different this time since there aren’t any results for Table1 (which are the first two columns shown in the Editor):
However, once you expand ‘NewColumn’, which is the second table, the items appear:
Now you can just select the first two columns and Delete them from the query so you’re left with just those names missing from the first list.
Download the Workbook
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.