Forum Discussion
Excel Formula for box numbers
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
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))- 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 14Let 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 7300Each 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.
