Nov 10 2020 10:35 AM
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.
Nov 10 2020 10:57 AM
Nov 10 2020 11:14 AM - edited Nov 10 2020 11:15 AM
@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))
Nov 10 2020 11:15 AM
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.