LookUp Batch Numbers Based on If the Cumulative Sum of the Batch Numbers is Equal or Greater to the Quantity Required for a Job Number

[LarsAustin]

Hello All,

I have this complicated problem (well, complicated for my limited excel skills) which I'm having a hard time solving.

I have a table in Sheet 1 listing planned production of products with different Production Order Number and Quantity.

In Sheet 2, I have a list of next level Work Order which will be needing the output (Quantity) from the table in Sheet 1.

What I want is a formula that will extract the Production Order No. from Sheet 1 that will satisfy the quantity requirement of the Work Order and put Production Order in cells D2:I4 (desired output in blue) in the table in Sheet 2.


For example, for Work Order N1001, I need to extract M15201, M15202, M15203, M15204 and M15205 (whose sum is 500) to cells D2, E2, F2, G2 and H2 respectively because its sum is enough to fulfill the Qty. Requirement of 450. And then it will be wise enough to know that there will 50 units ofM15205 that will be in excess which can the be use for the next Work Order N1002 and put that in cell D3 and extract M15206 and M15207 to cell E3 and F3.

I hope I explain my problem clearly enough.

Thanks

Lars


Sheet 1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Prod Order No.
[/TD]
[TD]<strike></strike><strike></strike>Item No_<strike></strike>
[/TD]
[TD]Description[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]M15201[/TD]
[TD]ABC01[/TD]
[TD]Product ABC[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]M15202[/TD]
[TD]ABC01[/TD]
[TD]

Product ABC​

<strike></strike>[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]M15203[/TD]
[TD]ABC01[/TD]
[TD]

Product ABC​

<strike></strike>[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]M15204[/TD]
[TD]ABC01[/TD]
[TD]

Product ABC​

<strike></strike>[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]M15205[/TD]
[TD]ABC01[/TD]
[TD]

Product ABC​

<strike></strike>[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]M15206[/TD]
[TD]ABC01[/TD]
[TD]

Product ABC​

<strike></strike>[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]M15207[/TD]
[TD]ABC01[/TD]
[TD]

Product ABC​

<strike></strike>[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]M15208[/TD]
[TD]ABC01[/TD]
[TD]

Product ABC​

<strike></strike>[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]M15209[/TD]
[TD]ABC01[/TD]
[TD]

Product ABC​

<strike></strike>[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Work Order[/TD]
[TD]Item No.[/TD]
[TD]Qty. Requirement[/TD]
[TD]Batch 1[/TD]
[TD]Batch 2[/TD]
[TD]Batch 3[/TD]
[TD]Batch 4[/TD]
[TD]Batch 5[/TD]
[TD]Batch 6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]N1001[/TD]
[TD]ABC01[/TD]
[TD]450[/TD]
[TD]M15201[/TD]
[TD]M15202[/TD]
[TD]M15203[/TD]
[TD]M15204[/TD]
[TD]M15205[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]N1002[/TD]
[TD]ABC01[/TD]
[TD]200[/TD]
[TD]M15205[/TD]
[TD]M15206[/TD]
[TD]M15207[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]N1003[/TD]
[TD]ABC01[/TD]
[TD]200[/TD]
[TD]M15208[/TD]
[TD]M15209[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello,

Your explanation is really perfect ...!!!

How large is your actual database ?

Are you about creating a Formula (or s set of Formulas..) to solve this question ?

 

[AlanY]

Hello All,

I have this complicated problem (well, complicated for my limited excel skills) which I'm having a hard time solving.

I have a table in Sheet 1 listing planned production of products with different Production Order Number and Quantity.

In Sheet 2, I have a list of next level Work Order which will be needing the output (Quantity) from the table in Sheet 1.

What I want is a formula that will extract the Production Order No. from Sheet 1 that will satisfy the quantity requirement of the Work Order and put Production Order in cells D2:I4 (desired output in blue) in the table in Sheet 2.


For example, for Work Order N1001, I need to extract M15201, M15202, M15203, M15204 and M15205 (whose sum is 500) to cells D2, E2, F2, G2 and H2 respectively because its sum is enough to fulfill the Qty. Requirement of 450. And then it will be wise enough to know that there will 50 units ofM15205 that will be in excess which can the be use for the next Work Order N1002 and put that in cell D3 and extract M15206 and M15207 to cell E3 and F3.

I hope I explain my problem clearly enough.

Thanks

Lars


Sheet 1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Prod Order No.
[/TD]
[TD]<strike></strike><strike></strike>Item No_<strike></strike>
[/TD]
[TD]Description[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]M15201[/TD]
[TD]ABC01[/TD]
[TD]Product ABC[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]M15202[/TD]
[TD]ABC01[/TD]
[TD]

Product ABC​

<strike></strike>[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]M15203[/TD]
[TD]ABC01[/TD]
[TD]

Product ABC​

<strike></strike>[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]M15204[/TD]
[TD]ABC01[/TD]
[TD]

Product ABC​

<strike></strike>[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]M15205[/TD]
[TD]ABC01[/TD]
[TD]

Product ABC​

<strike></strike>[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]M15206[/TD]
[TD]ABC01[/TD]
[TD]

Product ABC​

<strike></strike>[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]M15207[/TD]
[TD]ABC01[/TD]
[TD]

Product ABC​

<strike></strike>[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]M15208[/TD]
[TD]ABC01[/TD]
[TD]

Product ABC​

<strike></strike>[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]M15209[/TD]
[TD]ABC01[/TD]
[TD]

Product ABC​

<strike></strike>[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Work Order[/TD]
[TD]Item No.[/TD]
[TD]Qty. Requirement[/TD]
[TD]Batch 1[/TD]
[TD]Batch 2[/TD]
[TD]Batch 3[/TD]
[TD]Batch 4[/TD]
[TD]Batch 5[/TD]
[TD]Batch 6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]N1001[/TD]
[TD]ABC01[/TD]
[TD]450[/TD]
[TD]M15201[/TD]
[TD]M15202[/TD]
[TD]M15203[/TD]
[TD]M15204[/TD]
[TD]M15205[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]N1002[/TD]
[TD]ABC01[/TD]
[TD]200[/TD]
[TD]M15205[/TD]
[TD]M15206[/TD]
[TD]M15207[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]N1003[/TD]
[TD]ABC01[/TD]
[TD]200[/TD]
[TD]M15208[/TD]
[TD]M15209[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

since you split M15205 between N1001 and N1002, shouldn't you do the same for M15207 between N1002 and N1003?

 

[LarsAustin]

Hi AlanY,

Yes, the same should apply for M15207.

Cheers

Lars

 

[LarsAustin]

Hi James006,

Phew!! I thought i have not explained my predicament clearly

My source database is appoximately 30,000+ rows of records. A formula or set of formulas will help. I dont mind having helper columns as long as i can arrived with the correct batches (Production Order Nos.) in the specified cells in the table in Sheet 2.

I managed to extract the data for the first entry (N1001) in the table in sheet 2 by using a combination of Index, Small, Row, Rows and Columns which gives the answers in D2, E2, F2, G2 and H2. But I got stuck extracting the numbers for the succeeding entries.

Will really appreciate solution for this.

Cheers

Lars

 

[TedBeardsley]

Did you figure out the formulas for this to work? I currently have the same situation.

TD