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
[TABLE="width: 500, align: center"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 410"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Ship Date[/TD]
[TD] Ordered Qty, KG[/TD]
[/TR]
[TR]
[TD]A-1[/TD]
[TD]8/22/2012[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]A-1[/TD]
[TD]8/23/2012[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]A-2[/TD]
[TD]8/20/2012[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]A-2[/TD]
[TD]8/27/2012[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]A-2[/TD]
[TD]8/27/2012[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]A-2[/TD]
[TD]8/27/2012[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]A-2[/TD]
[TD]8/27/2012[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]A-2[/TD]
[TD]8/27/2012[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]A-2[/TD]
[TD]8/31/2012[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]A-2[/TD]
[TD]9/18/2012[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]A-2[/TD]
[TD]9/20/2012[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]A-2[/TD]
[TD]9/21/2012[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]A-2[/TD]
[TD]9/26/2012[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]A-2[/TD]
[TD]10/1/2012[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]A-2[/TD]
[TD]10/25/2012[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]A-2[/TD]
[TD]10/26/2012[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]A-2[/TD]
[TD]10/31/2012[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]A-3[/TD]
[TD]8/23/2012[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD]A-3[/TD]
[TD]8/24/2012[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]A-3[/TD]
[TD]8/27/2012[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]A-3[/TD]
[TD]8/27/2012[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]A-3[/TD]
[TD]9/4/2012[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]A-3[/TD]
[TD]10/12/2012[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]A-3[/TD]
[TD]1/3/2013[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]B-1[/TD]
[TD]8/15/2012[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]B-1[/TD]
[TD]8/15/2012[/TD]
[TD]96[/TD]
[/TR]
[TR]
[TD]B-1[/TD]
[TD]8/16/2012[/TD]
[TD]176[/TD]
[/TR]
[TR]
[TD]B-1[/TD]
[TD]8/16/2012[/TD]
[TD]176[/TD]
[/TR]
[TR]
[TD]B-1[/TD]
[TD]8/16/2012[/TD]
[TD]104[/TD]
[/TR]
[TR]
[TD]B-1[/TD]
[TD]8/17/2012[/TD]
[TD]128[/TD]
[/TR]
[TR]
[TD]B-1[/TD]
[TD]8/17/2012[/TD]
[TD]104[/TD]
[/TR]
[TR]
[TD]B-1[/TD]
[TD]8/21/2012[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]B-1[/TD]
[TD]8/27/2012[/TD]
[TD]128[/TD]
[/TR]
[TR]
[TD]B-1[/TD]
[TD]8/29/2012[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]B-1[/TD]
[TD]8/31/2012[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]B-1[/TD]
[TD]9/6/2012[/TD]
[TD]128[/TD]
[/TR]
[TR]
[TD]B-2[/TD]
[TD]8/15/2012[/TD]
[TD]128[/TD]
[/TR]
[TR]
[TD]B-2[/TD]
[TD]8/17/2012[/TD]
[TD]128[/TD]
[/TR]
[TR]
[TD]B-2[/TD]
[TD]8/28/2012[/TD]
[TD]128[/TD]
[/TR]
[TR]
[TD]B-2[/TD]
[TD]9/24/2012[/TD]
[TD]128[/TD]
[/TR]
</tbody>[/TABLE]


Tab -2: Production

[TABLE="width: 247"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]Production Date[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]8/13/2012[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]8/19/2012[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]8/29/2012[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]9/5/2012[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]9/10/2012[/TD]
[TD]150[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.