Excel formula to break table into multiple tables based on value-CodePudding

I have a table which part information, and I want to have it broken into various pallets based on the max number of containers allowed on a pallet. I use helper Column F to try and assign a pallet number, but its not taking into account that 8 containers is the max per a pallet.

Formula in F10: =ROUNDUP(SUM($C$10:C10)/$K$7,0)

Formula in J10: =FILTER($A$10:$D$15,(E10:E15=1)*(F10:F15=1))

While this is an Excel sheet, I uploaded it into Drive in case its easier to view the problem

CodePudding user response：

For J18:

=LET(δ,FILTER(A10:D15,(E10:E15=1)*(F10:F15=2)),κ,SCAN(0,INDEX(δ,,3),LAMBDA(α,β,α β)),FILTER(δ,κ<=K7))

Copy to other relevant cells, amending as required.

Note that the 3 in the part

INDEX(δ,,3)

generates the range reference

C10:C15

i.e. the third column in the range passed (A10:D15).