Solutions and training for business users of Microsoft Excel.
Solutions and training for business users of Microsoft Excel.

 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  
 
   
     
   
     

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.

Madeline,

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

Copyright 2004 - 2012 by Charles W. Kyd, all rights reserved. Content, graphics, and HTML code are protected by US and International Copyright Laws, and may not be copied, reprinted, published, translated, hosted, or otherwise distributed by any means without explicit permission. Terms of Use | Privacy Policy | Earnings Policy.

Excel Dashboards

Create professional quality dashboard reports with Excel.