Excel User's Home ExcelUser Blog Site Map Contact Excel for Business Excel Dashboards Excel Solutions Exploring Excel BI for Excel Business Tools Excel Catalog Affiliate Program Excel Help Portal

# 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.

I 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 usually do it is to use the MATCH function. This is quick and easy to do.

The MATCH function has this general format:

=MATCH(lookup_value,lookup_array,match_type)

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 your duplicates.

Here's the formula for the cell shown:

A4:   =MATCH(B4,\$D\$3:\$D\$7,0)

If you have a long list and you want to make it easier to see the matches, you can use:

A4:   =IF(ISNA(MATCH(B4,\$D\$3:\$D\$7,0)),"","xxxxx")

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,

Charley

ExcelUser, Inc.
http://www.ExcelUser.com