Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jun 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Merge / purge (Office XP)

    I have a project which is beyond my skill set. I have one excel file that has approximately 11,000 rows of data (name, contact information, etc.) and have a second excel file with approximately 3,000 rows of data, all of which are contained in the first file. I would like to find a way to combine the data and obtain a new list or file that simply has the remaining 8,000 lines (rows). The fields (columns) on the excel spreadsheets are identical.

    Many thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Merge / purge (Office XP)

    Welcome to Woody's Lounge!

    On which column or columns do you want to match the lists? Is there some kind of ID column, or do we have to compare column by column?

  3. #3
    New Lounger
    Join Date
    Jun 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge / purge (Office XP)

    The "duplicate" records will be located in rows, meaning that 3,000 of the 11,000 records will likely show up twice (on two rows.) I'm interested in obtaining those records who do not have a duplicate. In addition, I'm unclear if I'm to try to merge the two documents, and how to go about doing so.

    Many thanks in advance for any assistance.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Merge / purge (Office XP)

    I'm trying to find out how to determine whether a row in one sheet is a duplicate of a row in the other sheet. Can we compare a single field (column) in both rows, or do we have to compare each field in one row to the corresponding field in the other row?

  5. #5
    New Lounger
    Join Date
    Jun 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge / purge (Office XP)

    My apologies for not understanding your question. Ideally, we would like to compare two columns (first name, last name).

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Merge / purge (Office XP)

    In the attached workbook, you'll find two worksheets: Long List and Short List. All names in Short List also occur in Long List, although not in the same order.
    The macro Subtract will make a copy of the Long List sheet, then remove all rows from the copy that have a match in the Short List sheet.
    It uses columns A and B to match.
    When the macro is done, the new sheet will contain the names that do not occur in Short List.

    Warning: on large lists, the macro will be slow.

  7. #7
    New Lounger
    Join Date
    Jun 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge / purge (Office XP)

    I suspect that my knowledge is even less than I had anticipated. I do not know how to utilize macros, and in the attachment you provided, the macros were disabled and are required to be signed......

    Do I need to start with the macro issue before I can proceed?

    Again....many thanks.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Merge / purge (Office XP)

    Select Tools | Macro | Security.
    Set macro security to Medium. (High is too restrictive, Low isn't safe).
    Then click OK.
    Make sure you download the workbook I attached to your hard disk, don't open it directly from the post.
    When you open the downloaded workbook, you should be prompted to enable macros.

    To run the macro: select Tools | Macro | Macros, select Subtract in the list and click Run.
    To see the macro code: press Alt+F11 to activate the Visual Basic Editor.

Posting Permissions

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