Finding duplicate records in lists in Excel

Finding duplicate records in lists in Excel

Didn't find your answer?

Does anyone know a way of finding duplicate records in two lists (or a single list) in Excel, without using MS Access?
Tom Trainer

Replies (9)

Please login or register to join the discussion.

avatar
By AnonymousUser
15th Jun 2001 17:14

ISNA is useful.
The great thing about using Match is that you can then decide what to do if a record is found eg If(isna(match(A2,BB,0)),"","Duplicate") will display "duplicate" whenever one is found.

Thanks (0)
avatar
By tomtrainer
15th Jun 2001 10:13

#N/A
Sorry guys - I've just discovered ISNUMBER. Panic over!

Thanks (0)
avatar
By tomtrainer
15th Jun 2001 10:03

Problems with #N/A
Thanks guys. I have a small problem now in totalling the columns with "#N/A" and numbers in them. As I am sure you know, they won't total. Any ideas?

Thanks (0)
aw_logo_2019
By Accounting WEB
14th Jun 2001 12:02

Dominic - you're right!
Except that my solution gives a value for the macthed records (hastily trying to justify a duff idea!)

Thanks (0)
aw_logo_2019
By Accounting WEB
14th Jun 2001 08:24

For two lists...
Use vlookup to try and match values in one list with the other. Eg:
Say list1 is a1:a10 and list2 is d1:d12
Sort list2
in b1 enter =vlookup(a1,d$1$:d$12$,1,false)
copy this from b2 to b10
Duplicate records will show a value, non duplicates will show N/A

Thanks (0)
avatar
By AnonymousUser
14th Jun 2001 10:40

For Neil
You just invented a slower version of my Match solution.

Thanks (0)
avatar
By AnonymousUser
13th Jun 2001 19:45

Use Match for Two Lists
Say list one is in column A and list two is in column B. In another column (say C) test whether A2 is matched in B by putting into C2 = match(A2,B:B,0). This returns N/A if there is no match or the row number in column B if there is a match. Copy formula in C2 down.

Thanks (0)
avatar
By AnonymousUser
13th Jun 2001 19:49

Sort and Use Boolean for One List
Sort the list. Say list in in column A. Then use another column next to the list (say B). In B2 put =A1=A2. This returns True for duplicate and False for non-duplicate. Copy down column B. Then copy all the True and Falses and paste them back where they are in column B using Paste Special Values to freeze the values. Sort on column B. All the extra entries are in one place and can be deleted if that is what you want.

Thanks (0)
aw_logo_2019
By Accounting WEB
13th Jun 2001 16:48

A couple of simple options.
You could sort and sub-total the list using the "count" option. This should highlight duplicates where the count is greater than 1.

Alternatively, a pivot-table may well help identify them.

I'm sure there are a multitude of other methods, e.g., by using validation rules, in addition to these.

Thanks (0)