We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!Submit Feedback
Last week's challenge can be found HERE!
You have been given some order information that contains a list of orders with SKUs and related quantities. Additionally, you have a data set with the total inventory for each SKU.
You have been tasked with figuring out how to consume all of the inventory with the least amount of orders. In other words, you have been asked to fill the largest orders for each SKU in descending order.
Additionally, only complete orders may be fulfilled. For example, if an order calls for 4 units of a SKU, and only 3 are left in inventory, you can't fulfill the order.
this solution uses a basic "Fill in reverse size order" principle - but this doesn't guarantee maximum packing - with different order data, we may need to add an iterative macro or some other Knapsack process to maximize fill.
But for this input data, it matches the provided output.
I saw that many people have already provided similar looking solutions, so I decided to go down the iterative macro route that @SeanAdams mentioned. While I've created a macro that gets the correct answer and will allow for some more flexibility, it still has some major gaps. The iterative macro can be tweaked to find all combinations by unchecking the box (and then you could figure out the smallest set for sure), but it quickly blows up the processing because I haven't optimized it yet. It's not very well laid out, but here is my basic logic:
It was a good exercise, these challenges are so neat and definitely I learnt a new technique once again! 🙂
Mina, I just downloaded your workflow and looked at your multirow formula. I did kind of similar thing but I used first a multirow to compute the cumulative/ running total, then a formula to calculate the difference. I like that you did the same just using the multirow formula!
Actually, you could also simplify a little bit your Multirow, as the last IF statement is not needed and you could substitute it with [Row-1:Remaining], so your calc would look like:
(IF [Row-1:Order SKU]=[Order SKU] THEN [Row-1:Remaining] ELSE [Supply] ENDIF)-[Demand]>=0
THEN ((IF [Row-1:Order SKU]=[Order SKU] THEN [Row-1:Remaining] ELSE [Supply] ENDIF)-[Demand]) ELSE