I need to create 5 groups with an equal number of records and an equal sum

[CTRSME]

Hi Alteryx Community,

I am trying to create 5 groups of records with an equal number of records in each AND with an equal sum across the 5 groups (or as close as I can get to an equal sum). The markets contained in each group can be random. I have tried using the Multi-Field Binning tool and the Tile Tool but am not getting the desired solution.

For further context: I have a number of markets across England with a column showing total units consumed in each of those markets. I am looking to target marketing spend in 5 unique market groups with a similar sum of consumption to test which marketing approach works best.

I really appreciate any help I can get here!!

Thanks,
Caleb

[IraWatt]

Hey @CTRSME,

Your problem sounds a bit like the Knapsack Problem I'm having a look at Challenge #52: Solving the Knapsack Problem - Alteryx Community for inspiration. 

[smoskowitz]

Hi @CTRSME -- Sounds like a job for the Tile tool: https://help.alteryx.com/20221/designer/tile-tool

[PhilipMannering · Accepted solution]

@IraWatt and @smoskowitz. Think this might be more tricky than it appears. The Tile Tool can do equal sum or equal records but not necessarily both. What about sorting ascending and assigning groups 1, 2, 3, 4, 5, 1, 2, 3, 4, 5, 1, 2, ... etc. Not an optimum solution, but perhaps close enough?

[CTRSME]

I really appreciate everyone's response! As much as it would be great to have a perfect distribution, I think @PhilipMannering might have the simple solution that will work for now. I was going around in circles on this for ages so I really appreciate the simple solution! Thanks again for the help!!