Generate Product Pick List and transfer to Existing Workbook

[aaron.meza]

Hello,

I have a spreadsheet with two columns - one for quantity and one for product id. What I want to do is get a sum of how many units of a product I need to pull/make. for instance:

qty,id

2,product-1
1,product-2
1,product-1

would return a count of 3 for product-1 and 1 for product-2. If I can accomplish that with a macro it would make my life a whole lot easier. If I could also have this data transferred to our manufacturing schedule (I roast coffee to order) that would automate my morning tasks. For transferring to the schedule, a particular product id count would transfer to a specific cell in an existing workbook. The product list is discrete, so I could plug in which product id goes to which cell.

I am a programmer by trade but I've never worked much with excel and virtually no experience with VBA. I can't seem to figure out how to tackle this problem in excel. I've tried to use the Subtotal feature on the data ribbon menu but that only seems to count how many instances of a product id occur and not the qty associated with that Id in the same row. Any help in the right direction is appreciate. Sample data attached.

[MarvinP]

Hi aaron and welcome to the forum,

See the attached with what I think you might want.
1. I built your table in Col A and B and created a Pivot Table (5 seconds to do) to the right of it.
2. I though you might take orders ahead for the week so I put a new table with a date to the right
3. I built a Pivot Table using the Date as well as product and sum of bags of roasted coffee last.

Hope this helps you get some ideas.