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

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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
Joined
Oct 30, 2014
Messages
3,962
Office Version
365, 2019, 2016
Platform
Windows
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?
 

LarsAustin

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

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top