Tiling Help to Evenly Balance Sums within Groups

[kellja05]

Disclaimer: for privacy's sake, I have changed this data to fit a story different than it's actual use case. Hopefully this feels more like a weekly challenge to help understand the need I have.

Problem: Santa is sick and needs his elves to deliver gifts. 

In the workflow I have attached, one input contains a list of states, zip codes, and their populations. The other input has the director of elves. We need to give each elf a list of unique zip codes that they are assigned to visit to deliver gifts and we need for total population of houses to be as evenly balanced as possible across all of the elves. There are some nuances though that make this difficult to automate.

• Chimney-preferred zip codes can ONLY be assigned to chimney-abled elves
• All other zip codes can go to any elf
• Elf Managers should only have to visit ~1/3 of the total homes that the average chimney elf / door elf has
• They must stay within the zip codes of their assigned states

I've attached a workflow with some sample data in the style of a weekly challenge. I'd appreciate any help or guidance on the best way to approach this. I don't have experience with the tile or multi-field tile tools, but my gut says the best approach includes that and maybe some kind of iterative macro. 

[DylanDowrick]

Are Elf-Managers Chimney-abled?

Edit: It very clearly states in the dataset that they are not chimney-abled.

I have a starting point that ignores the 1/3 calculation for managers and treats them as Door_Elfs. 

This start file assigns each elf to either Door or Chimney and has a mostly equal population split.

Left to configure: 

• Setting Elf-Managers as 1/3 working value
• Actually assigning ZIPs into buckets of similar assignment size

[RWvanLeeuwen]

Great job sharing this thematic reprex!

I would suggest using locations of each Elf in your solution. You can use a spatial file for each ZIP as well. With both of these, you can connect a Find Nearest where the target is an elf, and the universe (where you find the nearest) is a ZIP. Then you can connect each one Elf with the ZIP that minimises the total sum of distances. I would suggest having a look at locatio optimiser macro functionality as well, but that is just to help delineate for you (and me both) that you will get this done using an iterative macro.

The iterative macro can find the 1st nearest ZIP for each Elf, and then use that ZIP that was found as starting centroid for the next iteration where the subset of ZIPs that are available in the Universe has been reduced (unmatched output from the find Nearest)...

This isn't easy, I agree, but if I have time later today, I will have a look into it

[chukleswk · Accepted solution]

It took some time.... but I think I have solved it!

Solutions Steps

• Iterative Macro Logic:
•  
  • Assign zip codes to elves iteratively until all houses are assigned while maintaining the constraints.
  • Inputs:
    • State, zip codes, population size (houses per zip code)
    • Elf type (Manager or Non-Manager flag)
    • Each elf's unique ID and the number of houses they've already been assigned
  • Process:
    • 1) Identify the elf with the fewest assigned housing (using a tiebreaker of the lowest Elf-ID if there's a tie)
    • 2) Sort the zip codes by population size in descending order
    • 3) Assign the largest unassigned zip to the selected elf and update their count of houses
  • Manager-Specific Processing:
    • For Manager elves, attempt to match zip codes with populations close to their maximum capacity
    • If no such match exists, revert to the standard process (steps 1-3 above)
    • Exclude the zip codes that would exceed the manager's maximum house limit
  • Termination condition:
    • The macro iterates until all zip codes are assigned

• Batch macro:
•  
  • The iterative macro is encapsulated within a batch macro that processes data separately for each state. This ensures:
    • Elves are only assigned zip codes within their state
    • No inter-state assignments occur

• Main Workflow:
•  
  • Step 1 - Preprocessing
    • Calculate totals: total houses, non-manager elves, and the maximum number of houses a manager elf can visit
  • Step 2 - Chimney Elves Assignment
    • Assign all chimney-preferred zip codes to chimney-abled elves exclusively
  • Step 3 - Manager Elves assignment
    • Assign zip codes to manager elves while ensuring their assignments do not exceed their calculated limits (done in step 1)
  • Step 4 - Remaining Assignments
    • Process the Remaining Zip codes (non-chimney) and assign them to the non-manager elves iteratively

• Performance considerations
  • The iterative macro requires joining all elves in a state with all zip codes, which can lead to a large number of records being processed. For example:
    • 14 elves and 200 zip codes = 2,800 records passed to the iterative macro
• Outcome:
• 
  • This solution ensures:
    • Chimney zip codes are assigned only to chimney-abled elves
    • Manager elves remain within their house limit
    • Assignments are balanced within a small margin of the state average (-0.04% to +0.07%)

[kellja05]

This is fantastic! Thanks for your help on this!

[Qiu]

@chukleswk 
It is amazing. 👍

I was using the Bin packing method to tacle this issue but had difficulties on the chimney-abled elves.