Multi-Product to Multi-Product Allotment problem

[earth2joy]

We have an issue in which a customer purchases and returns multiple product lines, and the contract requires debit and credit allocations to occur in a particular order by product pairs. The order of allotment cannot be revised. Some of us on the team believe an iterative macro is the only solution. Others believe there must surely be a simpler, more elegant solution. We've tried various combinations of sorting and multi-row tools, but the problem is that there is no way of knowing how many purchase-return pairs will exist, or in what order they will fall. We do have an iterative macro that works, but we want to satisfy that it is the only solution. I've attached a sample data set with two input tabs and a desired output tab, all containing relevant explanations of what's going on at each record.

Thanks Everyone!

Joy

[atcodedog05 · Accepted solution]

Hi @earth2joy 

Since this multi-source to multi-destination allocation iterative macro is the best approach for this resource allocation problem.

But let's see what others have to say.

[earth2joy]

I presented your response to the team. We all agree that if we are to provide the solution in Alteryx, an iterative macro is the way to go. 

Thanks!

Joy