Excel 2007 : Combining 2 large tables

**DougFane** · 09-10-2011, 09:49 PM

I am running an E-commerce store and my inventory is downloaded and uploaded using csv files. I also downloaded an inventory list (xls) from my wholesaler and sorted it alphabetically by product name and set it to include only items that are in stock and are of the type that I want. I organized my store's inventory the same way. What I need to do is this:

1. Whenever a product exists on both lists, overwrite the amount in stock in my store with the amount in stock from my wholesaler

2. Whenever a product exists in my inventory, set the amount in stock to 0

3. Whenever a product exists on my wholesalers list, but not mine, either ignore it, or append it to the end of my inventory list (this is not crucial, I am more worried about maintaining my current inventory)

My personal experience:

I have limited experience with PERL, more extensive experience with Java, C++, Python, and BASIC, but I have never programmed with Excel and I have no experience with sorting and searching algorithms other than what I have read. I honestly don't know where to start with Excel, but I really only need to perform this function.

**MarvinP** · 09-10-2011, 10:06 PM

Hi DougFane and welcome to the forum,

It is easier if both tables are in the same workbook, so I copied your wholesaler sheet to sheet 2 of the first. See attached.
In Column K I've put a VLookup formula to bring to the wholesaler sheet the number of parts tha match the discription from your export sheet.

See if that is of any help to you. Also - why are there so many #N/A showing the part on the wholesaler doesn't exist on the export sheet?

**DougFane** · 09-11-2011, 07:26 AM

Thank you Marvin for the worksheet provided. I also found another way to do what I needed to do. First I used an isna(matching( formula on both sheets which returned a set of boolean values to show which products existed on one list but not the other. I then sorted alphabetically and by boolean value so that the products that existed on both lists would be in alphabetical order at the top of the page. I then copied the available inventory column from the wholesaler sheet over the same column from my inventory. Then I set the available inventory to 0 for all my products that didn't exist on the wholesaler's sheet.

Note to Marvin:
The reason there were so many N/As in that column was that it has been a while since I updated my inventory, and many products I had have fallen out of stock, and new ones have come into stock.

Thank you all for your help and I hope this thread can be of use to others in the future