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

jtmcconnelljr

New Member
Joined
Nov 27, 2011
Messages
2
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

BigC

Well-known Member
Joined
Aug 4, 2002
Messages
851
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top