More times than I care to admit. I have found myself in a situation where I needed to compare two lists of serial numbers.
The first time I encountered this challenge it was thousands of serial numbers. Each time thereafter it was thousands of serial numbers.
Guess what?
Each time, I did not have a system to compare these two lists, there was a deadline for the analysis to be completed (usually the same day) I struggled to complete this task manually- oftentimes having to reach out for help.
If you are reading this. It is highly probable that you are in a comparable situation right now.
Please use this guide to make your life easier.
I have learned to use three formulas that work well together to help quickly identify which serials match and which don’t. This threesome will also tell us where the serial is located (which row) These three formulas can work together like a program to get you what you need.
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) system.
After the serial scans are complete, we would then 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 is a way this can be done.
Preparing the worksheet.
Open a blank Excel workbook and enter the part number that you are scanning as the header. In the next column enter the part number again.
You will need 5 columns
Label the column headers as such:
- Scanned Serial Number
- System Serial Number
- Match Formula
- VLOOKUP Formula
- Nested IF Error & VLOOKUP Combined
Your column headers should look similar to this:
Paste in your two lists of serial numbers. Paste one list in Column A underneath the “Scanned Serial Number” Column header and the other underneath the “System Serial Number” column
Now let us examine the three formulas that make the magic happen.
For this exercise we will use the following three formulas:
- MATCH – The Match formula will tell you what row the serial number is found in.
- VLOOKUP – VLOOKUP will tell you if the serial number is found by repeating the serial number that matches.
- IFERROR & VLOOKUP Combined – (When formulas are combined, they are referred to as “nested” formulas) The Nested IFERROR and VLOOKUP when combined will tell you whether the serial number is located by repeating the serial that matches. If there is an error (error #N/A) due to no match it will display the text, you write into the formula. I chose “NOT FOUND” for the text I wanted displayed If there was no serial number match.
Here is the syntax for each formula:
MATCH =MATCH(C4,B:B,0)
VLOOKUP =VLOOKUP(C4,B:B,1,0)
IFERROR & VLOOKUP =IFERROR(VLOOKUP(C4,B:B,1,0),”NOT FOUND”)
Now Copy and paste (or type) each formula underneath the column header. Once you enter each formula once you can use the fill handle to drag the formula down the column, so you do not have to type it over and over.
*NOTE* If your serial number list does not start in row 4 and starts in another row you will need to adjust the first part of each formula to match. So, if your serial number row starts in row 2 for column C your formula will need to have the numeral 2 instead of 4 as shown in the above example.
The finished product will tell you three things:
- What row the serial number is in (under the “Match Formula” column header)
- Whether or the serial number is found by displaying it. The error code #N/A will show if it is not found. (Under the “VLOOKUP Formula” column header)
- The text “NOT FOUND” (under the “Nested IFERROR & VLOOKUP Combined column header) is displayed for any serial numbers that are not found. This will make filtering and building reports easier and make more sense.
Final note
It is particularly important to make sure that all serial number values are stored as numerical values so the VLOOKUP will work. If the serial numbers that are pasted in the form are stored as “Text” the VLOOKUP will not work. If you paste your serial numbers as values only and then ensure the underlying text is a number, then your formulas should work fine when comparing values. If you are looking for a way to compare two lists of serial numbers without using formulas, then Power Query in Excel is a great tool. You can find the method here How to compare and extract data from two lists of serial numbers in Excel with Power Query – Larry Golden’s Digest! (larrygoldensdigest.com)
