CODEWITHSUNDEEP
excelexcel-formula
C West
C West

Reputation: 133

Excel formula to break table into multiple tables based on value

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 https://docs.google.com/spreadsheets/d/1dzf1lBIoQWttNFMxHX8uapXnJgRkaWfz/edit?usp=sharing&ouid=112271639578184342967&rtpof=true&sd=true Screenshot

Upvotes: 0

Views: 167

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9062

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).

Upvotes: 0

Related Questions