Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Location
    Reno, Nevada, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    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. #4
    New Lounger
    Join Date
    Feb 2002
    Location
    Reno, Nevada, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    New Lounger
    Join Date
    Feb 2002
    Location
    Reno, Nevada, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates (Excel 97/2000)

    Thank you very much for the help!

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •