I'm beginning to doubt that this is possible just by using Solver and my limited knowledge of VBA, but I figured it was worth asking.
What I'm trying to accomplish is a worksheet that calculates optimal sets of driving routes. I have a sheet of mileages set up as a matrix, with pick-up location names on the Y axis and drop-off location names on the X axis. When drivers are going from a pick-up location to a drop-off location, it's measured as "loaded miles". When they're going from a drop-off location to a pick-up location, they're "unloaded miles". I want solver to maximize "Loaded Miles/Unloaded Miles".
To do this, it needs to pair sets of 2-3 routes per row. For instance, "A to B", "C to D", and "E to F" will all be on one line. Loaded Miles will be equal to "(A to B)+(C to D)+(E to F)", and unloaded miles will be "(X to A) + (B to C) + (D to E) + (F to X)" with X being a fixed location. The major constraint is that the amount of times each individual location is used must be equal to a number calculated on another worksheet. That's already taken care of, but it's the reason that I'm trying to use Solver for this: the best possible route can't be used in every instance.
Everything is in place to handle this calculation if I can get Solver to populate the variable cells with strings (or rather, references to strings) in an "A to B" format. The mileage columns will break up the "route" strings that Solver puts into the variable column into "A" and "B" and pull data from the mileage matrix.
Is there a way to accomplish this? I imagine the actual spreadsheet would be a much better explanation than my rambling. I'll try to get a copy of that up tomorrow.
Thanks for your time.
Bookmarks