Merge product quantity from one sheet with quanity of same product from another

[jtmcconnelljr]

Hi - this is my first post here.

I am using two intuit based systems for our small pottery shop. Unfortunately, the two systems do not talk. So, I need to update the quantity of my products posted in my on-line store with the correct quantity of products in my brick-and-mortar store. I can create excel exports from both systems. The problem is the on-line system creates multiple entries for the same product and when I update the quantities I need to update all entries (usually 3-4 different entries depending on the number of categories I placed the product in). Is there a way to replace the incorrect quantities listed on my on-line export (which are listed multiple times) with the correct quantities from my brick-and-mortar system (which only lists the product once). I do have a common value SKU # on both systems that could be used as a reference.

Thank you for your help

 

[BigC]

In the qty cell for each of the on-line store products (doesn't matter whether it's one of the multiple entries for a product or not) use the following formula (not exactly like this is written, but using the same syntax):
=sumif('[bricks&mortar.xls]Export'!SKU_#,'[on-line.xls]Export'!SKU,[bricks&mortar.xls]Export'!SKU_Qty)
Basicaly, look in the list of B&M SKUs, find the SKU of this particular product, and sum the qty of the matching items (even though only one occurence will be found).

If using XL2007+, when you enter =SUMIF( Excel will prompt you for the arguments required by this function, from which you can then point/select.

 

[jtmcconnelljr]

wow - awesome! Thanks BigC.
The formula worked great.