Results 1 to 2 of 2

Thread: Merge Sheets

  1. #1

    Merge Sheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, struggling a bit here.

    I have worksheet1 that has a dump for a system and then some extra colomns for comments etc. One week later I take the dump again, this time some of the records will have gone, there will be some new ones and various will be the same record but with some changes.

    What I want to do is to update the old with the new but keeping my comments.

    Colomn A is the record number so I guess it could perhap use that to paste in new data, add new record to the bottom and then delete any that are not on in the new dump on sheet2.
    I hope this make sence, it would be a huge help to get this to work.

    Regards,

    Lee
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi there,

    Sheet1 is your master that you want to keep current, I'm assuming. My initial thought was doing something like this:

    • Rename Sheet1 to "Master" or something so it's more obvious what it is
      Rename Sheet2 to "NewDump" or something so it's more obvious what it is
      Retrieve your new set of data to the "NewDump" worksheet
      Add a VLOOKUP to the Master sheet, looking up the work order number in the NewDump records. Anything that comes back #N/A indicates that the record has been cleared, and can probably be deleted from the Master
      Add a VLOOKUP to the NewDump worksheet, looking up the work order number in the Master records. Anything that comes back #N/A is a new record and needs to be copied over



    But after looking at it, I think I might be overthinking this. If you were just to perform a VLOOKUP from the "NewDump" sheet to look up the comments in the "Master" that might be enough. Anything that's new will come back with #N/A, which we can guard against using IFERROR. Anything that exists should pull the comments. Once the VLOOKUP is added, you can copy the column, paste it as values, and then paste over the master worksheet.

    Does that sound reasonable, or did I miss anything?

    Once you've got the flow of it happening, then adding a macro to automate the steps should be fairly easy.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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