Optimization Equal Sum Between Groups

[fitzgerald2022]

We have various customer accounts that are broken up by which state they are in and I have 3 employees that have to work all the accounts. How do I get the sum of the accounts as close to equal to each other without breaking up the states and across the 3 employees?

I tried using the equal sum Tile tool but it doesn't give me a very accurate output.

I also tried to use the optimization tool but I could configure it to what I need.

State	# of Accounts
AR	2
AZ	22
CA	28
CO	30
CT	1
FL	18
GA	1
IA	16
KS	2

[CharlieS]

The Tile tool can produce a simple version all by itself. Set the tile method to "Equal Sum", number of tiles to 3, and sum field to "# of Accounts". Sorting the records will change the outcome. 

EDIT: I missed your comment on the Tile tool. You're already past that. 

The best distribution of the sample data appears to be 40-44-36. My initial thoughts go towards creating and testing different combinations until the best scenario is selected, but there are n! ways to order n objects (9! is 362,880).

I'll have to think about the best way to achieve this, but using the R tool to utilize an R package will likely be the most simple route to take. Here's a Stack Overflow link that discusses this problem using R:

https://stackoverflow.com/questions/6104836/splitting-a-continuous-variable-into-equal-sized-groups

[jdunkerley79]

This is a very tricky problem to solve.

In your small case a brute force approach will work:

This won't scale to either large numbers of salespeople or a large number of states. 

For that, a greedy allocation is the best approach I can come up with but I do not know if mathematically this would always produce the correct answer. Happy to build a macro implementing this as needed.

[Claje · Accepted solution]

I happened to do a talk at Inspire 2018 in Anaheim on a very similar problem, so I have an example of an iterative macro that accomplishes this in a way that I think works pretty well, although there may be some differentiation in certain edge cases.  The advantage of this approach is that I believe it should scale more efficiently to large numbers of states or employees.

I've attached a yxzp with an example workflow and the macro here.  I come to the same result as @jdunkerley79 in this example.

[fitzgerald2022 · Accepted solution]

Worked and scaled perfectly. I just had to change Accounts from Byte to Int because some of my account are over 100. Thank you all for the help!

[jdunkerley79]

Mostly for my interest have attached my 'greedy' approach. It is exactly the same algo as @Claje's

Great job @Claje