Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
merging data from 1 excel file, into a master excel file
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
merging data from 1 excel file, into a master excel file
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. -- Allllen -- Allllen "motSwE" wrote: 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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
merging data from 1 excel file, into a master excel file
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. "Allllen" wrote: 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. -- Allllen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mail merging 2 workbooks??? | Excel Discussion (Misc queries) | |||
consolidation of tables in excel with text and figures | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
no data in excel file | Excel Discussion (Misc queries) | |||
Getting data from another excel file | Excel Discussion (Misc queries) |