Merge Sheets

LeeZee

New member
Joined
Feb 9, 2014
Messages
1
Reaction score
0
Points
0
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
 

Attachments

  • TF Full List - WK 7-1.xls
    486.5 KB · Views: 8

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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.
 
Top