merging data from 1 excel file, into a master excel file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

motSwE - 13 Sep 2006 16:27 GMT

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.

Reply to this Message

Allllen - 13 Sep 2006 17:56 GMT

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.

Signature

Allllen

--
Allllen

> 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
[quoted text clipped - 9 lines]
>
> Thanks.

Reply to this Message

motSwE - 13 Sep 2006 18:39 GMT

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.

> Here is a simple solution without programming. Make sure you have copies of
> your files in case something goes wrong.
[quoted text clipped - 20 lines]
> You can now just copy paste the remaining lines from your Sheet 2 into your
> master file at the bottom.

Reply to this Message