Challenge #65: Fulfilling Inventory Orders

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.

challenge_65_start_file.yxmd

challenge_65_solution.yxmd

Join

Preparation

Advanced

Data Analysis

Basic

Accepted answers

All comments

SeanAdams

nice one!

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.

Spoiler

challenge_65_SeanSolution.yxmd

BenMoss

Here's my solution

Loved that you made it sound so easy before the complete orders only caveat

Spoiler

challenge_65_BM.yxmd

NicoleJohnson

My solution.

Spoiler

challenge_65_NicoleJohnson.yxmd

JoeM

@BenMoss, I have to do at least one caveat.

toddhowl

Had to do this one twice. The first time through I had way too many tools, so tried it again with fewer. :-)

Spoiler

challenge_65_toddhowl.yxmd

MinaGO

My multirow is a bit crazy, I think it will be interesting to see what other people did. This was definitely a fun one! I never usually get to use multirow in such a way. Thanks Joe

Spoiler

challenge_65_start_file.yxmd

Simona

Spoiler

I struggled a bit to be honest as I did not realize that the sorting was essential before the multirow, so I kept having more records and I was not sure why. Then I read more carefully the description of the challenge and I realised my mistake. Very good one!

It was a good exercise, these challenges are so neat and definitely I learnt a new technique once again!

challenge_65_start_file.yxmd

Simona

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

[Row-1:Remaining]

ENDIF

DinoF

Hi here's my solution, it might not be the quickest and simplest one but at least it works.

Cheers

challenge_65_start_fileDF.yxmd

alex

challenge_65_AC.yxmd

MarqueeCrew

Joe,

While I solved the problem with more tools than some, I needed some extra tools because instead of a join I used find replace. Nesting logic in a multi-row formula could remove another tool, but once you're there and getting good results I opted for maintainability. Kudos to all.