Excel Tip: Identify Duplicates In Data Lists

Written by Mike Moore

May 21, 2013
Microsoft-Excel-TipsToday I was working with two reasonably long lists of data and I needed to quickly see whether data contained in List1 was also contained in List2. Excel® can help with this.

Assume two simple lists of data, below:

A B C
1 List 1 List 2
2 Jeff Mike
3 Larry Sam
4 Jill Tom
5 Kim Ted
6 Rebecca Joe
7 Jane Bill
8 Krystena Jeff
9 Tim Larry
10 Tim
11 Edwin
12 Gaurav

 

To quickly identify which data in “List 1” also appears in “List 2”, insert the following formula into cell C2, then copy that formula down through cells C3-C9 (i.e. the entire length of List 1):

=IF(ISERROR(MATCH(A2,$B$2:$B$12,0)),””,A2)

Note: A2 contains the data in the first list that we’re searching for in the 2nd list, $B$2:$B$12 represents the fixed location of the entire 2nd data set.

By inserting that formula in cells C2-C9, Excel will generate the result below.

If the data point in List 1 also appears in List 2 then Excel will display a match for List 1 in its same row but in Column C. If there is no match in List 2, a blank space will appear in the same row but in Column C.

A B C
1 List 1 List 2
2 Jeff Mike Jeff
3 Larry Sam Larry
4 Jill Tom
5 Kim Ted
6 Rebecca Joe
7 Jane Bill
8 Krystena Jeff
9 Tim Larry Tim
10 Tim
11 Edwin
12 Gaurav

 

Caveat: The data must be an exact match for the formula to generate the correct result. If the data lists contain any stray spaces or misspellings you may not get a perfect result; that is, “Mike” and “Mik_e” might be a match in fact, but Excel won’t identify it as such.

If you have a better or more efficient way to accomplish this result, please feel free to share.

RELATED POSTS:
Excel Tip: How To Create Simple Macros
Top 5 Reasons To Automate Legal Document Creation
Leave Your Mark With Outlook Signatures