If you are looking for a quick and easy way to compare two lists of serial numbers and extract data that does not match from a large data set with thousands of rows, then you can use Power Query in Excel to accomplish this feat. This method allows us to identify which serials match and which don’t without the manual hassle or purchase of expense software. Power Query can act like a robust program to get you the results you need swiftly.
Let’s say we are cycle counting a part number by serial number, and we need to scan each serial number and then compare those serial numbers with a list from our WMS (Warehouse Management System).
After the serial scans are complete, we need to quickly see what serial numbers matched and what serial numbers did not match. Ideally, we would accomplish this without having to manually identify or sort the list and try to pick out the mismatches from there.
Here’s a way this can be done using Power Query.
Step 1
Make sure the values in each list are in number format.
Step 2
Turn each list into an Excel table by selecting all data in cell A1 – A20 (Or whatever the range is for your first table) then select “Insert” then “Table” then select “Ok.” Congratulations, you have your first table. Now repeat this action by selecting all data in Cell D1- D20 (Or wherever the range is for your second table then select “Insert” then “Table” then select “Ok.”
You should have two tables that look like this:
Step 3
Put your cursor in the first table with the column header “Serial List 1” and select “Get Data” from the far-left ribbon underneath “File.” Why? We need to use the “clean” and “trim” function in Power Query to ensure there are no hidden or extra characters. This step is important to ensure that we can “combine queries” and our “merge” works successfully a little later on.
Step 4
After “Get Data” is selected the Power Query Editor will become visible. Go to “Transform” in the ribbon and then “Format” and finally select “Trim”
Step 5
Next, select “Clean” in the “Format” drop down selection list.
Step 6
Now that your two comparison tables have been cleaned and trimmed you can GOTO the ribbon and select “Get Data,” then “Combine Queries” and finally “Merge.”
Step 7
After selecting “Merge” you will need to select the tables AND columns that you want to compare. Once selected then select the “Join Kind” choose “Left Anti”
Step 8
By choosing “Left Anti” you will see the serial numbers in the first list that are not on the second list. They will show in a newly populated table on a new tab after you select “Close & Load” underneath “File” in the ribbon.
The new table will look like this on the “Merge1” default tab.
If you compare your original serial list 1 and serial list 2 to the output from the “Merge1” tab – you will find that Excel has merged the two lists for you and extracted the serials from list 1 that do not have a match in list 2. Therefore, by default the remaining serials from list 1 in your original list are serial numbers that are in list 2.
When you have a large complex file with thousands of rows of data, and you need to extract serials (or any other type of data) from one list that are not in another list quickly this is one of the most efficient methods to do so without doing it manually or spending any money for software. Furthermore, you can reuse the query once it is built. So, if the list ever expands and you need to perform this task again…… As long as all the file headers are the same, you simply need to update your tables and refresh the query. Once these actions are complete you will have your new list in seconds.
You can learn more about merging queries using Power Query in Excel here: Merge queries overview – Power Query | Microsoft Learn If you prefer to use formulas instead of Power Query, you can learn a quick and easy method here: How to compare two lists of serial numbers in Excel – Larry Golden’s Digest! (larrygoldensdigest.com)