Want to get involved? We're always looking for ideas and content for Weekly Challenges.
SUBMIT YOUR IDEA
This one for being a beginner one took me a few minutes to get my head wrapped around. But once I took a step back and thought about how to solve it, the solution came relatively easy (though I had to tweak my order of operations until I was happy...)
First thing I did was join the store and product data to get my assortment per store, then I sorted them by 1) Item and 2) Priority to get everything in the order I wanted. At this point, I joined in the warehouse data to get the amount of each product available (for the start of each item). I then use a multi row formula to update the available (accounting for the row-1 being null at the start of each product) and then simply begin to decrement the available balance for each stop. I then distribute the amount for the next store, remove unnecessary rows and then sort the file back into the store/item order. I have also added a quick validation block to verify my answers (not shown). I wanted to do this without Macros (which was mentioned in the original post) because while I understand Macros have a place, for me it is more important to allow the tool to be more transparent to the designer and macros become very library like to me, very black box (which for some things make sense) so I like to use them sparingly and when that is really the only choice or it is a process that I need identical over multiple flows.