merging 2 spreadsheets

[Guest]

I have 2 spreadsheets - a master spreadsheet and sheet #2. Sheet #2 contains
all of the information from the master (with the same exact columns), plus
new rows that are inserted throughout. The first column of data uniquely
identifies each row. Most of the rows are identical in both, with some
tweaks to the master. Therefore, I do not want any cells in the pre-existing
rows to be overwritten in the master.

I'd like to merge in just the new rows from sheet #2 into the master, and
leave everything that is already in the master (and has been tweaked), as is.

How do I do that?

Thanks.

 

[Guest]

Here is a simple solution without programming. Make sure you have copies of
your files in case something goes wrong.

In sheet 2, add a new blank column next after your first (reference) column.
Use vlookup in this column as follows:
=VLOOKUP(A2,[MasterBookName]MasterSheetName!$A:$A,1,0)

The result will be #n/a for any rows which are not in the master. If the
rows are in the master, you will just get a repeat of what is in column A.

Now if you have a huge file, I recommend Copy and Edit > Paste Special >
Values on this new column B. Select the whole column and paste the values
over itself. That just gets rid of the vlookup formula and fixes the values
in place.

Now do a sort on the new column B, so that you get all the #N/As together.

Delete all the rows that don't have this #N/A. You aren't interested in
them. They are already on your master file.

Delete the column B that you created (the one with the #N/As in it).

You can now just copy paste the remaining lines from your Sheet 2 into your
master file at the bottom.

 

[Guest]

I actually do something relatively similiar right now but need a way to
automate the whole process instead of manually inserting, copy/pasting, and
deleting.

Also, for the new rows that are in sheet2 and not in the master, I need them
to be inserted in the master exactly as they are ordered in sheet 2, by
column A. Both the master and sheet2 are both in the same order, except for
the new rows, which are scattered throughout.

Any way to program this?

Thanks.