List Duplicates or Missing Values By Comparing Two Columns In Excel
Comparing Two Lists With An Excel Formula
Sometimes the real world provides us with situations where we need to compare lists. Whether it's comparing an attendance list for an event or trying to audit that values exist in both sets of data. These sorts of comparison tasks are far too often manual when they don't have to be.
Let's take a look at how you can use a single Excel formula to dynamically curate a list of values that are shared and not shared between two data ranges on your spreadsheet.
Formula For Identifying Non-Shared Values Between Ranges
Let's first tackle isolating values between two contact lists that are not shared. Or said another way, the names only show up in one of the two lists.
In this formula solution, we'll use the Dynamic Array Function UNIQUE in combination with VSTACK in order to compile our list of non-shared values.
Excel Formula From This Example:
=UNIQUE(VSTACK(UNIQUE(B4:B18), UNIQUE(D4:D21)), , 1)
How This Formula Determines Shared Values
UNIQUE(B4:B18) and UNIQUE(D4:D21): This removes any duplicates from the list.
VSTACK(B4:B18, D4:D21): The VSTACK function vertically stacks two arrays/ranges. In this case, it stacks the range of names from B4:B18 on top of the range from D4:D21. This results in a single-column array that contains all names from both ranges, one after the other.
UNIQUE(..., , 1): The UNIQUE function then takes this stacked list of names and returns a list of unique names. The third argument of the UNIQUE function, which is set to 1 (or TRUE), specifies that it should return unique rows. Since the data is already in a single column due to VSTACK, this argument effectively removes duplicate names from the combined list, regardless of their original position in the two input ranges.
The result of this formula is a single-column array of unique names from both the B4:B18 and D4:D21 ranges, eliminating any duplicates between or within these two lists.
Formula For Identifying Shared Values Between Ranges
Now let's take the opposite approach and determine contacts in the example data that exist in both lists. In this formula solution, we'll use the Dynamic Array Function FILTER along with ISNUMBER and MATCH to assist with the logical testing.
Excel Formula From This Example:
=UNIQUE(FILTER(B4:B18, ISNUMBER(MATCH(B4:B18, D4:D21, 0))))
How This Formula Determines Shared Values
MATCH(B4:B18, D4:D21, 0) looks for each value in range B4:B18 within the range D4:D21 and returns their position in D4:D21 if found. If not found, MATCH returns an error.
ISNUMBER(…) checks whether the MATCH function's output is a number. This will be TRUE for values found in both columns and FALSE for those that aren't.
FILTER(B4:B18, …) then filters range B4:B18's values, only returning those for which the second parameter (the ISNUMBER(MATCH(…)) part) is TRUE.
UNIQUE(...) removes any duplicate values that may be within the list you are filtering on
Download The Excel File I Used
I Hope This Microsoft Excel Article Helped!
Hopefully, I was able to explain how you can write formulas that utilize Dynamic Array functions to compare two ranges and isolate shared and unshared values. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.