how to merge two excel files?

Question

Here is what we are looking to do..

We have two seperate excel documents. On both documents we have a column called "productcode" & "inventory", they list the number of stock for a specific product. Is it possible to have one excel file update the other excel file by replacing the "inventory" number for that specific productcode?

To further explain. Our online file would list a product code of "testproduct" & a an "inventory" number of 99.

We then want to take another seperate excel file that has the product code of "testproduct" & an "inventory" number of 10. Then somehow this file would search for the code "testproduct" on the original excel file & update the "inventory" number to 10

Appreciate any & all help!

I should add more detail to my question. The excel that we need to update consists of around 1200 products.

The excel file that we are using to update that files consists of over 3000 products. That is why we need something that will match productcodes on the file that has to be updated with current stock amounts

Answer 1

<>Assuming for the sake of the example that you have your "productcode" data starting with the header in A2 and the "inventory" starting in B2 and the "testproduct" data in Row 6 on both workbooks. Also, you named the data range on the second workbook "newdata." If you want the updated inventory info to appear alongside the existing data, in C6 on the first workbook enter:
=VLOOKUP(A6,Test1A.xls!newdata,2)
Test1A = the name of the second file.
This will give you the updated inventory number entered on the second book (10), in this case named "Test1A," placed alongside the original inventory (99). Note: You must have unique data entries for this to work. This will list the update beside the original inventory, not overwrite it.

Answer 2

Merge Two Excel Files

Answer 3

You may be trying to hammer a nail with a screwdriver.

This problem is really best solved with a database. If you have Microsoft Access, you can do it by linking to the two excel sheets, then use a simple update query to change the inventory amounts.

Once the links are made, the update query is run, the Excel sheets are updated and you can open them with Excel or look at them as linked tables in Access too.

In Access, do file, link and choose the excel type and set up both excel sheets as linked tables in Access.

The update query has some tricky stuff in it, but it is powerful.

You create a new query, using the table you want to update, put in the code (for searching) and the inventory (for updating)

In the field with the code, you put in the criteria row the name of the new inventory file and the code field name like this: [tablename].[code]. In the Update to row of the inventory field you put the table name and the inventory field like this: [tablename].[inventory]. You run the query and it is all done.

You mentioned that the update from sheet has more records. Are there duplicate codes and if there are how do you account. Do you change the inventory to the total of all the codes, or are there just extra, un used codes?

If there are duplicates, you may want to summarize in a separate excel sheet so there are not duplicates and then update from that one.

Good Luck!

Answer 4

This Site Might Help You.

RE:
how to merge two excel files?
Here is what we are looking to do..

We have two seperate excel documents. On both documents we have a column called "productcode" & "inventory", they list the number of stock for a specific product. Is it possible to have one excel file update the other excel file by...

Answer 5

Hello My Dear
You Can Go to Data ,menu and select Import External Data and select Import Data and Browse Your File to import in this WorkSheet

and you can refresh datasource

this is very easy

good luck
bye
mj_professional

Answer 6

It is something like druid said...

BUT
If you have "File1.xls" that you want to type the name in and search for it..
And "File2.xls" that has the total list of "productcode" & "inventory" values in Sheet1 cells A1:R18

Then

Type the "testproduct" value you want to search for in B2
paste this function in C2
=vlookup( B2, [file2.xls]sheet1! $A$1:$R$18, 2, false)

Sure there are alot of other ways..

contact me through Yahoo! Answers if you like

Read my profile, I am the XLMan

-
I still need more info
send me the details and file on [e-mail]

believe me, if it isn Excel, then I can solve it

XLMan