Excel Formula for box numbers

%3CLINGO-SUB%20id%3D%22lingo-sub-1873522%22%20slang%3D%22en-US%22%3EExcel%20Formula%20for%20box%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1873522%22%20slang%3D%22en-US%22%3E%3CP%3ENeed%20assistance%20to%20setup%20an%20box%20nr.%20pack%20list%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20maximum%20quantity%20that%20can%20be%20packed%20in%20a%20box%20is%201500%20as%20per%20the%20list.%20After%20the%20first%20box%20of%20the%20first%20variety%20is%20filled%20up%2C%20any%20incomplete%20amounts%20would%20be%20filled%20up%20by%20borrowing%20from%20the%20next%20variety%20in%20line%20i.e.%20if%20the%20balance%20is%20200%20for%20Jenny%20for%20box%20nr.%203%2C%20then%20this%20will%20be%20filled%20up%20by%201300%20Sylvia%20which%20is%20the%20next%20variety.%3C%2FP%3E%3CP%3EI%20have%20attached%20an%20example%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1873522%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1873628%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20for%20box%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1873628%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20got%20the%20issue%2C%20however%20your%20requirement%20is%20not%20clear%20enough%2C%3CBR%20%2F%3Ewhat%20will%20happen%20for%20the%20variety%20in%20the%20next%20line%20such%20as%20(G6%3AI6)%2C%20would%20you%20remove%20it%3F%20value%20in%20H5%20will%20be%201500%3CBR%20%2F%3E%3CBR%20%2F%3EDo%20you%20want%20it%20to%20be%20like%20this%3A%3CBR%20%2F%3E%3CBR%20%2F%3EJenny%201%2C500%201%3CBR%20%2F%3EJenny%201%2C500%202%3CBR%20%2F%3EJenny%201%2C500%203%3CBR%20%2F%3ESylvia%201%2C500%204%3CBR%20%2F%3ESylvia%201%2C500%205%3CBR%20%2F%3ESylvia%201%2C500%206%3CBR%20%2F%3EAurora%201%2C500%207%3CBR%20%2F%3EAurora%201%2C500%208%3CBR%20%2F%3EAurora%201%2C500%209%3CBR%20%2F%3EHanna%201%2C500%2010%3CBR%20%2F%3EHanna%201%2C500%2011%3CBR%20%2F%3EHanna%201%2C500%2012%3CBR%20%2F%3EHanna%201%2C500%2013%3CBR%20%2F%3EHanna%20100%2014%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1873728%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20for%20box%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1873728%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F481153%22%20target%3D%22_blank%22%3E%40A_SIRAT%3C%2FA%3E%26nbsp%3BI%20have%20attached%20a%20file%20with%20an%20equation%20based%20table%20next%20to%20your%20desired%20table.%26nbsp%3B%20I%20added%20a%20column%20to%20you%20original%20table%20called%20**bleep**%20Ord%20for%20the%20cumulative%20total%20of%20ordered%20parts.%26nbsp%3B%20I%20also%20did%20not%20take%20into%20account%20the%20individual%20'Max%20Pack%20rate'%20since%20you%20said%20it%20is%20max%20of%201500%20and%20I%20didn't%20know%20what%20to%20do%20if%20product%20Jenny%20had%20been%20max%201500%20but%20Sylvia%20was%20only%20500%3B%20would%20line%204%20be%20500%20or%20something%20less%20since%20there%20is%20already%20200%20Jenny%20in%20the%20box.%26nbsp%3B%20That%20said%20the%203%20equations%20are%3A%3C%2FP%3E%3CP%3EBox%20Number%20(col%20K)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(MOD(SUM(M%241%3AM2)%2C1500)%2CK2%2CK2%2B1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVariety%20Name%20(col%20L)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DXLOOKUP(SUM(M%242%3AM2)%2B1%2CTabelle1%5B**bleep**%20Ord%5D%2CTabelle1%5BVariety%20Names%5D%2C%22error%22%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBox%20nr%20(col%20M)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMIN(XLOOKUP(SUM(M%242%3AM2)%2B1%2CTabelle1%5B**bleep**%20Ord%5D%2CTabelle1%5BQty%20ordered%5D%2C%22error%22%2C1)-SUMIF(L%242%3AL2%2CL3%2CM%242%3AM2)%2C1500-MOD(SUM(M%242%3AM2)%2C1500))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Need assistance to setup an box nr. pack list

 

The maximum quantity that can be packed in a box is 1500 as per the list. After the first box of the first variety is filled up, any incomplete amounts would be filled up by borrowing from the next variety in line i.e. if the balance is 200 for Jenny for box nr. 3, then this will be filled up by 1300 Sylvia which is the next variety.

I have attached an example file.

3 Replies
Hello,

I got the issue, however your requirement is not clear enough,
what will happen for the variety in the next line such as (G6:I6), would you remove it? value in H5 will be 1500

Do you want it to be like this:

Jenny 1,500 1
Jenny 1,500 2
Jenny 1,500 3
Sylvia 1,500 4
Sylvia 1,500 5
Sylvia 1,500 6
Aurora 1,500 7
Aurora 1,500 8
Aurora 1,500 9
Hanna 1,500 10
Hanna 1,500 11
Hanna 1,500 12
Hanna 1,500 13
Hanna 100 14

@A_SIRAT I have attached a file with an equation based table next to your desired table.  I added a column to you original table called **bleep** Ord for the cumulative total of ordered parts.  I also did not take into account the individual 'Max Pack rate' since you said it is max of 1500 and I didn't know what to do if product Jenny had been max 1500 but Sylvia was only 500; would line 4 be 500 or something less since there is already 200 Jenny in the box.  That said the 3 equations are:

Box Number (col K):

 

=IF(MOD(SUM(M$1:M2),1500),K2,K2+1)

 

Variety Name (col L):

 

=XLOOKUP(SUM(M$2:M2)+1,Tabelle1[**bleep** Ord],Tabelle1[Variety Names],"error",1)

 

Box nr (col M):

 

=MIN(XLOOKUP(SUM(M$2:M2)+1,Tabelle1[**bleep** Ord],Tabelle1[Qty ordered],"error",1)-SUMIF(L$2:L2,L3,M$2:M2),1500-MOD(SUM(M$2:M2),1500))

 

 

 

@Jihad Al-Jarady 

 

Let me explain myself better.

 

I receive an order like this ;

Variety ID   Variety Name    Quantity
10102          Jenny                3200
11102          Sylvia                5200
10002          Aurora              3900
10062          Hanna              7300

 

Each box can take a maximum of 1500.

So box nr.

1 = Jenny 1500

2= Jenny  1500

3 = Jenny   200

3 = Sylvia  1300

4 = Sylvia 1500

5 = Sylvia 1500

6 = Sylvia 900

 

and continuation.