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. 

Santa's Helpers.yxmd

chuckleswk

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%)

Santa's Helpers.yxzp