Home > Exploring Excel
Find Duplicates in Excel Lists
"How do I determine what entries in my very simple spreadsheet
contain matching values in 2 columns? Simple example: a list of names,
addresses, etc., each with two codes; some of the names have the same
code in both columns, but some do not." -- Madeline C.
assume your worksheet looks something like the one shown here. You have
two lists and want to know which items are duplicates.
There probably are several ways to find the duplicates. The way I
it is to use the MATCH function. This is quick and easy to do.
The MATCH function has this general format:
If you use a match_type of 0 (zero) Excel looks for the lookup_value
in the lookup_array. If it finds the value in the array (or list) it
returns the index number of the first matching item in the list;
otherwise, it returns #N/A.
Therefore, all cells in column A that have a numeric value refer to
items in column B that have duplicates in column D. That is, they show
Here's the formula for the cell shown:
If you have a long list and you want to make it easier to see the
matches, you can use:
This formula returns a null string (a blank) if no match is found.
Otherwise, it returns "xxxxx".
In either case, you could sort on this column to move the matching
items into a group that you can work with more easily.
All the best,