# 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
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

 A B C D 1 Prod Order No. Item No_ Description Quantity 2 M15201 ABC01 Product ABC 100 3 M15202 ABC01 Product ABC​ 100 4 M15203 ABC01 Product ABC​ 100 5 M15204 ABC01 Product ABC​ 100 6 M15205 ABC01 Product ABC​ 100 7 M15206 ABC01 Product ABC​ 100 8 M15207 ABC01 Product ABC​ 100 9 M15208 ABC01 Product ABC​ 100 10 M15209 ABC01 Product ABC​ 100

<tbody>
</tbody>

Sheet 2

 A B C D E F G H I 1 Work Order Item No. Qty. Requirement Batch 1 Batch 2 Batch 3 Batch 4 Batch 5 Batch 6 2 N1001 ABC01 450 M15201 M15202 M15203 M15204 M15205 3 N1002 ABC01 200 M15205 M15206 M15207 4 N1003 ABC01 200 M15208 M15209

<tbody>
</tbody>

#### James006

##### Well-known Member
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

##### Well-known Member
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

 A B C D 1 Prod Order No. Item No_ Description Quantity 2 M15201 ABC01 Product ABC 100 3 M15202 ABC01 Product ABC​ 100 4 M15203 ABC01 Product ABC​ 100 5 M15204 ABC01 Product ABC​ 100 6 M15205 ABC01 Product ABC​ 100 7 M15206 ABC01 Product ABC​ 100 8 M15207 ABC01 Product ABC​ 100 9 M15208 ABC01 Product ABC​ 100 10 M15209 ABC01 Product ABC​ 100

<tbody>
</tbody>

Sheet 2

 A B C D E F G H I 1 Work Order Item No. Qty. Requirement Batch 1 Batch 2 Batch 3 Batch 4 Batch 5 Batch 6 2 N1001 ABC01 450 M15201 M15202 M15203 M15204 M15205 3 N1002 ABC01 200 M15205 M15206 M15207 4 N1003 ABC01 200 M15208 M15209

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

#### LarsAustin

##### New Member
Hi AlanY,

Yes, the same should apply for M15207.

Cheers

Lars

#### LarsAustin

##### New Member
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

1,081,981
Messages
5,362,533
Members
400,679
Latest member
alecalec202

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...