Help Needed: Match Production to Open Orders

[scorpio0077]

Hi,
I work for a manufacturing plant where we manufacture one product which is then sold under different names.

The product is made from the same material.

I am looking for a way to match up production to the orders and then subtract the produced quantity from the orders.

Tab 1 - Orders: Column 1 has the items (A1, A2 and A3 are all produced from A), Column 2 has the date when the order has to ship and Column 3 has the ordered volume.

Tab 2 - Production: Column 1 has the raw material, Column 2 has the production date and Column 3 has the amount manufactured.

Any help would be highly appreciated!!

Tab -1: Orders

<tbody>
</tbody>

Item	Ship Date	Ordered Qty, KG
A-1	8/22/2012	16
A-1	8/23/2012	160
A-2	8/20/2012	20
A-2	8/27/2012	70
A-2	8/27/2012	160
A-2	8/27/2012	160
A-2	8/27/2012	160
A-2	8/27/2012	80
A-2	8/31/2012	20
A-2	9/18/2012	50
A-2	9/20/2012	120
A-2	9/21/2012	160
A-2	9/26/2012	160
A-2	10/1/2012	40
A-2	10/25/2012	160
A-2	10/26/2012	50
A-2	10/31/2012	160
A-3	8/23/2012	63
A-3	8/24/2012	180
A-3	8/27/2012	180
A-3	8/27/2012	45
A-3	9/4/2012	180
A-3	10/12/2012	180
A-3	1/3/2013	180
B-1	8/15/2012	120
B-1	8/15/2012	96
B-1	8/16/2012	176
B-1	8/16/2012	176
B-1	8/16/2012	104
B-1	8/17/2012	128
B-1	8/17/2012	104
B-1	8/21/2012	24
B-1	8/27/2012	128
B-1	8/29/2012	24
B-1	8/31/2012	16
B-1	9/6/2012	128
B-2	8/15/2012	128
B-2	8/17/2012	128
B-2	8/28/2012	128
B-2	9/24/2012	128

<tbody>
</tbody>

Tab -2: Production

Item	Production Date	Quantity
A	8/13/2012	140
B	8/19/2012	40
A	8/29/2012	120
B	9/5/2012	40
A	9/10/2012	150

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Skybluekid]

In table 2 you have only A's and B's is this correct? as you A-1 etc in table 1

 

[scorpio0077]

@Skybluekid: You're right.

A-1, A-2, A-3 are all different packages of A.

 

[Robert Mika]

@Skybluekid: You're right.

A-1, A-2, A-3 are all different packages of A.

What's the relation beetwen those values and what's your expected result?

 

[pjmorris]

Hi, if you use SUMIF you can summarise both the orders and the production. To select all the variants referred too in the order column concatenate "*" with the cell reference containing A or B etc. So if your order data spans A1:C41 and the summary of orders is in G2 use =SUMIF($A$2:$A$41,$F2&"*",$C$2:$C$41), where $F2&"*" looks for anything in the data starting with the character in cell F2 (A or B etc). The summary of production, which I placed in cell H2, is obtained from Sheet 2 with =SUMIF(Sheet2!$A$2:$A$6,Sheet1!F2,Sheet2!$C$2:$C$6).

Hope this helps.