How to distribute work evenly but with individual set limits. Iterative macro?

Question

Let us say you have two inputs.

input one, a list of items that need to be distributed

input two, a list of people to distribute them too, but with a limit on how many they can take

Let's say you want to randomly sort input one, then distribute to the list of people, up to their limits. Any remainder after everyone has reached their limits goes to a final overflow list.

The distribution limit is where I run into issues. If the distribution was even, I could just use a tile tool or a multirow tool.

but I need to add in something so that when a person hits their distribution limit, it cuts them off.

I tried doing a second multi-row tool to divide out any element > than the distribution limit, but it doesn't work as wanted. Because what I really need is for the distribution to continue until everyone reaches their limit so that the overflow are the ones left at the end.

I'm sure there is a way to use an iterative macro to do this, but I don't know how. Hoping someone can help. I am attaching example files of some fake data in anyone cares to lend a hand.

example of data to be distributed.xlsx

example of distribution list.xlsx

PHinkel · Accepted Answer

Hi @rfoster7 ,

Take a look at the attached workflow and let me know what you think.

You can use the Generate Rows tool to create a row for each "open slot of work" that a team member has and then Join on Record Position to assign that work with the non-joined work falling into the "Overflow" bucket.

We already randomly sort, so it may not matter that we assign work to one person up to capacity and then the next, but if this ever becomes a concern you could sort the output of Generate Rows by RowCount to assign all "RowCount = 1" lines first.

Feel free to reach out with any questions!

SPetrie · Accepted Answer

Here is another option using an iterative macro as you were initially thinking. It randomized the list and then assigns it to each tech and drops them off after their limit is reached.

Because all items go through the iteration, the two inputs go through a union beforehand.

Assigned jobs come out of the assigned anchor and over flow jobs come out the other.

More involved than the other solution, but more fun to create too. So there is that :)