Duplicates (Excel 97/2000)

Topic

New Reply

We have a user with a spreadsheet with over sixty thousand names on it and would like to compare it with another spreadsheet with over eight hundred names. She would like to compare the two sheet and identify the duplicate names. Is there some way to do this without importing it into Access? (I know, that would be the application of choice for this example!) Our end user does not know how to use Access and we have been unable to schedule time to teach her. Thanks for your assistance in this matter… My e-mail address is shanep@softcom.net

Reply | Quote

Replies

Chip Pearson explains several methods to find duplicates and to compare lists in Duplicate And Unique Items In Lists.

Reply | Quote

Thank you very much for the help!

Reply | Quote

Hi Shane,

Probably the easiest method would be to use a formula like:
=countif([BigList]Sheet1!$A$1:$A$60000,A1)
in the smaller workbook, where
[BigList]Sheet1!$A$1:$A$60000
defines the list in the large workbook, and
A1
is the cell in the smaller workbook with the entry to be compared.

You would then copy this formula down all 800 rows in the smaller workbook. For any cell in the smaller workbokk that appears in the larger one, this formula returns a number equal to how many times the entry appears in the larger one. You could then sort the smaller workbook on the column with this formula so as to group all the duplicated entries together.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I’ve just been given two worksheets at the office, one has 45,000 entries and one has 12,000 entries and my first thought was to come here with a question about finding the duplicates. But the search feature brought up your pointer and I will try it. Thanks.

Reply | Quote

60,000 names? Phew! Might be a bit of a stretch for Excel with a 65536 row limit, and depending on your processor.
If this is a one-off situation, I could do it for you if you’d like (using another application)…otherwise, all the best with Chip Pearson’s methods which are always sure-fire!

Reply | Quote

Thanks for your kind offer. I will try the Parson method first. I do belive this is a one time thing.

Reply | Quote