Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Consolidating Worksheets (Excel 2000)

    I am uploading an example file given me by a student of mine. We are having problems consolidating the information from sheet one and sheet two onto sheet three. I would like to know if anyone could help us out here. He also will be watching this thread if you can help, so we'd sure appreciate it. We have been using the Consolidation command, and though his data isn't the same on each sheet we are aware consolidation should still join the info from all sheets onto sheet 3 without any duplicates. We "are" getting duplicates.
    Thank you,
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Consolidating Worksheets (Excel 2000)

    Hi NMP,

    See if the attached does something along the lines of what you're after. Sheet 3 attempts to consolidate Sheets 1 & 2, using the SSN on Sheet 2 as the key. You'll notice a few rows of #N/A - due to SSN diiferences between Sheets 1 & 2 for people who are apparently the same. You'll also notice that the cells in Sheet 3 have some numbers left-aligned and others right-aligned. That's because some of the 'numbers' on Sheets 1 & 2 were input as text. Having done the basis set-up, I then moved the columns around a bit to 'pair' most of the like data from both sheets.

    Although I've used INDEX/MATCH formula combinations, and one could use LOOKUP formulae instead, I'm not sure what's been achieved that couldn't be done with a simply copy & paste.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidating Worksheets (Excel 2000)

    Thank you. I am sure that this student is keeping an eye on this thread. Will have him download the example you've given and see if he can work with your suggestions.

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    New Lounger
    Join Date
    Sep 2004
    Location
    Jacksonville, North Carolina, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidating Worksheets (Excel 2000)

    Thank you for the assistance. This does look to be getting towards what is needed for the first step of this project. However, please notice the last names in Column E as compared to the last names in Column B. After the first #NA they seem to be out of sync. Is there a way to fix this?

    Now for the big question. How do I take what you have done on this example sheet and make it work on the two other files that I need to put togather?

    Thank you again very much for you help and taking the time to teach me!

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

    Re: Consolidating Worksheets (Excel 2000)

    Hi perrymedik,

    The mismatching occurs for the same reason as the #N/A - your SSNs on the two sheets don't agree. For example, SSN 345678912 is assigned to ACUNA RAMON on Sheet1 but to ADAMS DANNY WAYNE
    on Sheet2. If you are going to consolidate the data and get a meaningful result, these sorts of errors will have to be corrected.

    For your other files, the approach would be essentially the same, except that the sheet references might become file and sheet references unless you add the data from those files to (new) sheets in this workbook. And, to consolidate the data, each of those files would need to have certain unique data in common with at least one of the others and/or the data in the workbook you already have. For the example I posted, I chose SSNs as the key because they should be unique to each person.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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