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.

0
motSwE (2)
9/13/2006 3:27:02 PM
excel.newusers 15348 articles. 1 followers. Follow

2 Replies
466 Views

Similar Articles

[PageSpeed] 7

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.
> 
0
Utf
9/13/2006 4:56:01 PM
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

> 

0
motSwE (2)
9/13/2006 5:39:02 PM
Reply:

Similar Artilces:

Outlook 2003 PRF File
Hi, Does anyone know if it is possible to set-up multiple profiles in a PRF files. We have a document management product that required a second profile in order to interact and so when Outlook is started we would like it to set-up the user profile and this additional profile, so 2 profiles. I know that you can set-up multiple services but we would like to have multiple profiles. Thanks, -- Jonathan Thomas Hampshire County Council Each PRF file can set up only one profile, and Outlook can process only one PRF file per Outlook session. Therefore, you can do this, but it will require ...

5.4.6 and 5.1.0 NDRs
Hello, We have setup our first Exchange 2003 server, in a an Exchange 5.5 org, and sent a test email to all of our mailboxes to see if everything was ok from the Exchange 2003 server. However, 144 mailboxes returned a 5.4.6 NDR and several returned a 5.1.0 NDR message. Does anyone know how to resolve this? As usual my boss wants Exchange 2003 setup company wide yesterday, so any info would be much appreciated. Thanks in advance Ste Sounds like some kind of routing loop. Can you post some information regarding the corresponding event log entries and your Exchange design. Nue &quo...

merging
Is it possible to create a document in publisher (mine is a certificate) and then merge the desired information from a select querry in access? Hi Tina (sttpreston@clyde.k12.oh.us), in the Microsoft� newsgroups you posted: || Is it possible to create a document in publisher (mine is || a certificate) and then merge the desired information from || a select querry in access? Yes, you can. Which version of Publisher and Windows are you using? -- Brian Kvalheim Microsoft Office Publisher MVP Official Publisher MVP Site: http://www.kvalheim.org This posting is provided "AS IS" wit...

Tabbing in Excel
Simple question: Is there a way to edit the direction of the Tabbing feature. Building a spreadsheet with locked/unlocked cells and need to change the default of "right to left" to "down then over". Any help? reply to : jerry.creacy@acs-inc.com oops. Default should be "left to right" >-----Original Message----- >Simple question: Is there a way to edit the direction of >the Tabbing feature. Building a spreadsheet with >locked/unlocked cells and need to change the default >of "right to left" to "down then over". Any he...

Merge same transaction
I've had transactions that I've placed in my register with detailed info in the memo area only to have the same exact transaction download with no info and unrecognized as the same transaction. Traditionally I've copied and pasted the info into the downloaded transaction and then deleted my transaction. This is a real PAIN... Is there a way to tell Money that these two separate transactions are the SAME transaction and then have it merge them? It works exactly as intended when Money recognizes the transaction that's already in the register and then merges them beautiful...

Linking Cells: Excel into Word Document
Greetings, After reading through the help files for Excel 2002, it seems to me that there is no way, except for programming, to have just a range of cells in a worksheet be linked to a Word document. Here is what I am attemping to accomplish: Using Word to create an extensive document for a database proposal. Using Excel to document the all of the required tables (since nothing has been created in Acces and I am new databases), drawing diagrams with lines and other database objects. Would like to be able to insert/link certain cells from a worksheet (which would be many inserts/links) into t...

Microsoft Visual Studio Installer 1.1
Hi all, Where can i download the Visual Studio Installer 1.1 for Visual Studio 6? http://msdn.microsoft.com/en-us/vstudio/aa718352.aspx The link is not work. I need to create deployement package in VS6. Many thanks!! Martin "Martin" <martin@hotmail.com> wrote in message news:OJyzRz%23aKHA.5608@TK2MSFTNGP05.phx.gbl... > Hi all, > > Where can i download the Visual Studio Installer 1.1 for Visual Studio 6? > http://msdn.microsoft.com/en-us/vstudio/aa718352.aspx > The link is not work. > I need to create deployement package in VS6. It ...

creating an address data base #2
I would like to create a database of clients, and when i type ne invoices i would like to select them so the address etc will fill int the address block automaticly -- adspreadboroug ----------------------------------------------------------------------- adspreadborough's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1565 View this thread: http://www.excelforum.com/showthread.php?threadid=27192 ...

Importing data from Maximizer
Hi: What experience has anyone had with importing Maximizer data into MSCRM using either the Data Migration tool, Scribe, or a combination of both? Scribe don't handle this seamlessly, you apparently need to export to a .csv file first. Is this still quicker using this process with Scribe, than with the DMF? Many Thanks Cathy Allington Hi, We are also looking at migrating Maximizer into CRM. If you would not mind, could you please let me know if you find anything more on this. I would truly appreciate it. Shauna skoppang@shaw.ca "Cathy Allington" wrote: > Hi: > ...

Word mail merge
After printing a document there exists a activity with subject "Word Mail Merge" to the contact with status completed. Is there a possibility to change the subject name or to make a link to the original Word document? R, Joop. I think that the subjct name is hardcoded. The issue of only adding this paltry information to the activity and not showing what document was sent is a problem many have asked about. Microsofts workaround to this glaring gap in functionality is to then add a copy of the word document to the record using notes. Totally unworkable if you have just mail merg...

Pasting picture files to Excel
Whenever I paste ia jpg picture nto Excel (or Word for that matter), I get only 3 choices (bitmap, metafile (bigger than bitmap), and photoediting (bigger than both)) . My question is, is there anyway to paste in a picture to an Excel file, but keeping it compressed as a jpeg file, this way the excel file size doesn't grow by leaps and bounds? Thanks. Matt: Since Excel has the ability of editing a picture, the software has to limit its pictures to those formats that MS has editing tools to work with. That's why you can't easily set up JPG or GIF pictures as embedde...

Free! Learn MS Excel
The following is a video that demonstrate to you: How to plot a chart with Microsoft Excel. http://raymondlamsk.blogspot.com/2008/05/free-learn-microsoft-excel-how-to-plot.html I hope it helps you in one way or another. ...

How do I use 2 sets of data in a chart
both sets of data are in the same workbook, but on two different worksheets. Hi, Have read of Jon's page http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html Cheers Andy Skeety wrote: > both sets of data are in the same workbook, but on two different worksheets. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Outlook 2010
In Outlook 2003 I had one .pst file with all contacts inside, but used 3 email accounts with no automatic rules for mail delivery. I manually put the emails down in it's respective archive.pst files and want it done this way. In Outlook 2010 when using several email accounts, it's automatically established one .pst file for each email account where the respective email drops into. Is there a way to have it all into one "Personal Folder" (the old way)? (I want to send emails were sender account is the only on showing as sender and not any "sent on behalf ...

Excel "file is already open"
Whenever I open a '.xls' workbook from outside Excel (such as from Windows Explorer), Excel indicates that the file is already open - although, once I click OK, I seem to be able to work with the file normally. The same file will open fine from within Excel. I am using Excel 97 on an XP-Home machine. Can you explain why I may be getting this message, and how I might correct for it? The FAQ at Debra Dalgleish's site might give the solution: http://www.contextures.com/xlfaqApp.html#AlreadyOpen John McFadden wrote: > > Whenever I open a '.xls' workbook from ...

Merging 2 Companies into 1
Are there any guidelines or outline for what tables you need to look at to gather the various information you need in order to merge 2 companies together as well as issues to consider when undertaking such a project? It is a project and a half. I got approval from a major client yesterday to proceed with just this project. You literally need to look at all of them! I understand that MS Professional Services will do this project for you. In my case the client cannot be down and has too many 3rd party products for us to pass it off. When does your project need to be completed? -- Ri...

1 Easy Question Sorry for all the posts this should be it
I found a great white paper on recovering exchange and basically says the steps everyone has told me. However, I have one last question. I just found out I don't have a system state backup. I did have to reinstall windows on top of the existing to get it to boot up. I know I can reinstall exchange over the existing and then work with the exchange databases off the white paper. I am just wondering will not having the correct system state kill me? Because I am seeing an issue like I can't remote desktop with terminal services running. Is this system state truly critical of my data? ...

mail merge/merging 2 address lists...
Is there a way to merge two of your address lists in Publisher 2002? I have two address lists in my data base folder and would like all the addresses merged into one as there are some addresses different in each and some the same. Thank you. Robin Hi Robin (vicary@kconline.com.NOSPAM), in the Microsoft� newsgroups you posted: || Is there a way to merge two of your address lists in Publisher 2002? || I have two address lists in my data base folder and would like all || the addresses merged into one as there are some addresses different || in each and some the same. No, you cannot. You nee...

adding data from multiple spreadsheets into one chart
I need to put information from 4 different spreadsheets into one graph. Can someone please help me? Produce a graph from your first spreadsheet. Copy the relevant data range from the second spreadsheet, then select the graph and edit/ paste special/ new series or new data points. Similarly for the third and fourth sheets. If you have difficulties getting exactly what you want that way, you can use edit Source Data in the graph to add an extra series or to change the X or Y data range for an existing sreries. -- David Biddulph "Marjory" <Marjory@discussions.microsoft.com&g...

merge and compare
We have two versions of the same work sheet from differnt dates. Some of the cells have been changed in the latest version and we need to compare which cells have had changes and selectively merge the two. Is there any hope??? jimired wrote: > We have two versions of the same work sheet from differnt dates. > > Some of the cells have been changed in the latest version and we need > to compare which cells have had changes and selectively merge the two. > > Is there any hope??? Hi have a look at http://www.cpearson.com/Zips/Compare.ZIP An add-in which compares two worksh...

How can I get bar codes w/ Excel & mail merge?
I want to send out a bulk mailing with large postcards. I want the addresses printed on the postcards via mail merge. I am using Excel, but don't see where or how to add the bar code to the address, which would save me a lot of money. Can you help? >-----Original Message----- >I want to send out a bulk mailing with large postcards. I want the addresses >printed on the postcards via mail merge. I am using Excel, but don't see >where or how to add the bar code to the address, which would save me a lot of >money. Can you help? >. > hi, I not entirely...

Any way to shift data axes?
Helo all, I am having problems getting some data into the format I need. I'm wondering if this is even possible actually. I'm sure it is, but I'd prefer to keep things simple. Data is coming in from a linked Excel table in a format like the following MRC VEH 1 2 3 4 5 101 AAA 12 34 56 78 90 101 BBB 12 34 56 78 90 101 CCC 12 34 56 78 90 102 AAA 12 34 56 78 90 102 BBB 12 34 56 78 90 102 CCC 12 34 56 78 90 103 etc... MRC and VEH denote the cost centre and vehicle types. For those combinati...

Merging worksheet into MS Word mail merge
I am merging a letter and an Excel worksheet. When I merge an Excel field that contains $ and commas separating the hundreds and thousands, the $ and the , do not show up in the merged letter. Any suggestions on how to solve this problem? -- Briggs Hi Instead of just referring to the cell e.g. A1 use =TEXT(A1,"[$$-409]#,##0.00") -- Regards Roger Govier "Briggs" <Briggs@discussions.microsoft.com> wrote in message news:2F326201-A50F-45E5-9767-4C189ACA5529@microsoft.com... > I am merging a letter and an Excel worksheet. When I merge an Exc...

Error in Mail Merge when selecting Edit Individual Labels
We are noticing an interesting error when trying to perform a mail merge within CRM and Microsoft Word. We have created a view in contacts and we perform all the typical mail merge functionality using the Labels option. At the end, instead of printing the labels without any edits, we click on “Edit Individual labels” and we get the following error… C:\Program Files\Microsoft Office\Office12\OUTLOOK.EXE has encountered a problem and needs to close. We are sorry for the inconvenience. We are on CRM v4 Update Rollup 8 using Outlook 2007 and the CRM Outlook (Online only) client. Has an...

use of Randbetween() in Excel
Hi to everyone. I had 10 columns in Excel. A1 B1 C1 ……J1 ..... … … ……… … … … ……… A50 B50 C50 j50 Each column uses the Randbetween() function to create integers 1-100, that is Randbetween(1,100). Is there anyway to use somehow the function so the integers in every row to be different? (I mean: A1 <> B1<> C1…….<>J1, A2 <> B2 <> C2…..<> J2,……………, A50 <> B50 <> C50 …….<> j50) Thank you. http://...