I really haven't progressed far in this myself as I'm not exactly sure about the best way to go about solving it (so no code really done on my end).
I have two unique sets (let's call them Set A and Set B) of 90 locations. Each of these two sets is further subdivided into 30 subsets of 3 locations each. Each of these locations is defined by a name which I am then using to pull a zip code that will then define that location and pulling the latitude and longitude of each zip code. This part is working correctly.
Each subset of 5 ties to another subset of 5 locations from the other grouping of 100 (eg 1A, 2A, 3A are going to be grouped with with 1B, 2B, 3B). I want to assign one location from group A to a location in group B such that the total distance traveled for each subset of 4 is minimized (eg 1A to 2B, 2A to 3B, 3A to 1B). I want to wrap this all up in nice VBA code so that someone just has to click play and it finds the minimized distance for each of the 30 subsets.
Right now, the only way I can see to solve it doesn't involve solver (because it has to pick from a named location as a variable cell) but actually solving for each distance combination and then finding the one that allows the smallest distance. That seems extremely clunky to me and just wanted to see if someone might have a better thought process.
I have two unique sets (let's call them Set A and Set B) of 90 locations. Each of these two sets is further subdivided into 30 subsets of 3 locations each. Each of these locations is defined by a name which I am then using to pull a zip code that will then define that location and pulling the latitude and longitude of each zip code. This part is working correctly.
Each subset of 5 ties to another subset of 5 locations from the other grouping of 100 (eg 1A, 2A, 3A are going to be grouped with with 1B, 2B, 3B). I want to assign one location from group A to a location in group B such that the total distance traveled for each subset of 4 is minimized (eg 1A to 2B, 2A to 3B, 3A to 1B). I want to wrap this all up in nice VBA code so that someone just has to click play and it finds the minimized distance for each of the 30 subsets.
Right now, the only way I can see to solve it doesn't involve solver (because it has to pick from a named location as a variable cell) but actually solving for each distance combination and then finding the one that allows the smallest distance. That seems extremely clunky to me and just wanted to see if someone might have a better thought process.
