Optimizing when to Start Projects over a year to Evenly Distribute Hours

Question

I am attempting to evenly distribute projects over a year so that the number of hours in each month are almost the same for each month. However, the projects are also assigned to a specific "domain" and can stretch over the course of multiple months. So, I am attempting to redistribute the projects over a year in a way that for each "domain" the sum of hours is as close to even as possible for each month. But if a project is originally from March to June (4 months) the project can be moved to August to November (4 months) but it can't be split up over non-sequential months.

The workflow/macros that I attached currently does what I am looking for, but it takes forever to run with my actual data which has 127,000 rows. I am hoping someone can help me optimize the macro, currently it is creating all possible combinations and narrowing it down from there, so it gets slower exponentially with every additional record.

EvenDistributionData (1).xlsx

EvenDistribution_Final.yxmd

EvenDistribution.yxmc

EvenDistribution_batch.yxmc

DanM · Answer

@ColinMarc

I took a look at your setup and based on what i see it looks pretty efficient. Understand that the more purple and orange tools you have on the canvas take up more memory. I did find something that I am not quite understanding in your EvenDistribution.yxmc. I see that your S Input is then connected to a Formula tool which creates the iteration # and then is joined to the Input which has your project numbers. What I think is happening is on every iteration it's starting back at 1 and going around and around until it meets each project number. I would consider looking at that piece of it and working to find a solution that doesn't cause it to have to iterate through all of these iterations that are not going to produce anything. I see that in the other Input you're working with the engine iteration so you have two iteration variables within the flow. It also seems that you're just doing joins within the iterative macro so I'm not seeing an actual reason to iterate through this piece. It may be easier to create a batch macro in this case, but not fully knowing your use case that might not be what you want. Sorry that I don't have a complete answer or solution but based on what I see I think the iterative macro might be where to start.