In Part 2 we identified the hubs Santa will visit this season and the minimum weight that can deliver presents to every kid in those hubs with respect to their present score.
But what about maximizing the space of the sled so that it’s full,
while accounting for how much weight the reindeer can pull?
Can you help the elves revisit the present assignments for each nice kid now that we know how many reindeer Santa is attaching to the sleigh this year? They want to make sure every kid is getting the biggest and best (priciest then heaviest in priority order) present they earned in their present classes. The kids who behaved the best should be the first to get their presents adjusted - they earned it!
Goal of Part 3:
Determine the exact present distribution of the nice kids without exceeding 422 lbs per hub - prioritize price, then weight and assign to the nicest kids first
This was a bit of a challenge....I'm guessing others experienced the same since this is the first response.
I started with a cartesian join of sorts where all kids in each present class were assigned all possible gifts and then ran it through an interative macro progressively removing the better gifts, re-ranking etc, for the kids in each hub from best to worst. I had a built in criteria that would allow each kid to receive their "worst" gift before going onto the next child. When the weight came in under 422 pounds for the hub, the macro would write the output. Over 422 pounds and it would go back through the workstream.
While I think I was onto something - I got it to a point where I was caught in an endless loop with only a few records left! I must have had a hub or two where my methodology never got below 422 lbs.
Hoping others can provide Santa more help!
I've attached my workflow, macro, and data in a zipped file.
I've also failed Santa... I've attached an attempt to simply process one hub using the optimizer tool with dynamically generated constraints (e.g. for the hub in question: required number of presents in each present category and the max allowed weight)... once working this would still need to be wrapped in an iterative macro. If a solution along these lines is revealed, I look forward to it! Brain = fried! (Kaggle shares blame for that, though.)
A little late, but here is my solution. After thinking about ways to approach this problem, I decided to take an interative waterfall approach. It's convoluted, and not optimized by price, but it's the only way I could think of.
I would have liked to learn to use the Optimize tool for this, but I guess that will have to wait for the New Year's Resolution.
Solution attached - we packed every inch and corner of Santa's vehicle - in fact, if he breathes too deeply something may fall out...