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

New Member
Joined
Feb 27, 2016
Messages
16
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

ABCD
1Prod Order No.
<strike></strike><strike></strike>Item No_<strike></strike>
DescriptionQuantity
2M15201ABC01Product ABC100
3M15202ABC01
Product ABC
<strike></strike>
100
4M15203ABC01
Product ABC
<strike></strike>
100
5M15204ABC01
Product ABC
<strike></strike>
100
6M15205ABC01
Product ABC
<strike></strike>
100
7M15206ABC01
Product ABC
<strike></strike>
100
8M15207ABC01
Product ABC
<strike></strike>
100
9M15208ABC01
Product ABC
<strike></strike>
100
10M15209ABC01
Product ABC
<strike></strike>
100

<tbody>
</tbody>

Sheet 2

ABCDEFGHI
1Work OrderItem No.Qty. RequirementBatch 1Batch 2Batch 3Batch 4Batch 5Batch 6
2N1001ABC01450M15201M15202M15203M15204M15205
3N1002ABC01200M15205M15206M15207
4N1003ABC01200M15208M15209

<tbody>
</tbody>
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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 ?
 
Upvote 0
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

ABCD
1Prod Order No.
<strike></strike><strike></strike>Item No_<strike></strike>
DescriptionQuantity
2M15201ABC01Product ABC100
3M15202ABC01
Product ABC
<strike></strike>
100
4M15203ABC01
Product ABC
<strike></strike>
100
5M15204ABC01
Product ABC
<strike></strike>
100
6M15205ABC01
Product ABC
<strike></strike>
100
7M15206ABC01
Product ABC
<strike></strike>
100
8M15207ABC01
Product ABC
<strike></strike>
100
9M15208ABC01
Product ABC
<strike></strike>
100
10M15209ABC01
Product ABC
<strike></strike>
100

<tbody>
</tbody>

Sheet 2

ABCDEFGHI
1Work OrderItem No.Qty. RequirementBatch 1Batch 2Batch 3Batch 4Batch 5Batch 6
2N1001ABC01450M15201M15202M15203M15204M15205
3N1002ABC01200M15205M15206M15207
4N1003ABC01200M15208M15209

<tbody>
</tbody>

since you split M15205 between N1001 and N1002, shouldn't you do the same for M15207 between N1002 and N1003?
 
Upvote 0
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
 
Upvote 0
Did you figure out the formulas for this to work? I currently have the same situation.

TD
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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
Back
Top