1. ## Duplicates (Excel 97/2000)

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

2. ## Re: Duplicates (Excel 97/2000)

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

3. ## Re: Duplicates (Excel 97/2000)

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!

4. ## Re: Duplicates (Excel 97/2000)

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

5. ## Re: Duplicates (Excel 97/2000)

Thank you very much for the help!

6. ## Re: Duplicates (Excel 97/2000)

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

7. ## Re: Duplicates (Excel 97/2000)

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•