HELP! How to use ITERATIVE MACRO

[amruta]

Input: If I am given multiple food products. And the quantities of food bags varies in lbs (less than 10)

Problem statement: Assign each food bag a BOX NUMBER and each BOX cannot be greater than 10lbs, can be equal to 10lbs or as close as 10.

I have to fill up the boxes to fullest i.e.10 lbs. The boxes can be left partial (less than 10) only if there is no other option, else try to fill up to fullest. See trial workflow and sample input data attached and expected output.

How would you compute? please help.

Assumption - do not mix different food types.

[apathetichell]

i do not believe this is a batch macro or an iterative macro problem - this needs the optimization tool.

[SPetrie · Accepted solution]

You could do it with an iterative macro and a couple mluti-rows.

One multi-row (grouped by food type) to test the totals and another to identify the ones that move on to the box.

[Qiu]

@SPetrie  
Nice work.
I was acutally looking into this one, and learned it is a typical Bin packing problem.

https://en.wikipedia.org/wiki/Bin_packing_problem

Maybe I can try something different later. 

[JarekSkudrzyk]

@SPetrie brilliant solution!

[Qiu · Accepted solution]

@SPetrie 
lets submit it as a weekly challenge. 😁

I just noticed that the Bag (lbs) is already sorted in the input file, so maybe we can add one Sorting tool and it will be method of "Best Fit Decreasing".
https://www.youtube.com/watch?v=7QBwgI7h-zw

[SPetrie]

@Qiu Thats a great suggestion.

Thanks for the link as well. I thought I had heard of a problem like this before, but didnt realize there was an actual name for the type of problem :)

[Tyro_abc]

@SPetrie  - this is awesome, and I agree with @Qiu 's suggestion.

[amruta]

@SPetrie This is awesome, It works like a charm. Thank you so much for your time and help!

@Qiu Thank you for your suggestion.