Making a distance calculator with time optimization

[MasterSplinter]

I am trying to make a distance calculator with time opimization. Every point is marked with coordinates. The goal of this document is to make an attack planner for a strategy game.

This is what I got so far:

c86f8fa1ee4e8d9513b025ecae2d76d1.png

The first sheet displays information for my villages (points with coordinates)
The second sheet displays information for the enemy targets (also points with coordinates)
The third sheet (which is shown) is showing distance between a taget village (B1) and all of my villages.

Is there a way to use the hungarian algorithm or excel solver to find the best routes (min time between first and last hit) for my villages to hit x targets (yellow table to the right on the picture)?


The guy who tried help me set this up is not around anymore, and I am not able to finish it by myself. I have never used VBA or solver, so please be very specific when explanining Thank you so much in advance!

EDIT: If anyone would like to make the whole attack planner for me, I would be happy to pay for it! Take concact with email removed to discuss price and product, since this is a free forum. (I guess this is ok with the forum rules since making the planner for me is not what I am originally asking. If it is violating the forum rules, please remove this edit)

[Alf]

Your problem reminds me a bit about ”the travelling salesman” problem were a salesman should visit a number of cities in such an order that his total travel was the shortest possible.
Then of course the salesman of my days did not travel in nukes delivering death and destruction.
O tempora o mores!
As you have Excel 2013 you have the ”Evolutionary” solver engine and this youtube link shows a setup for the ”travelling salesman

https://m.youtube.com/watch?v=-E3rSoClgMI


As the excel solver models are not that powerful (by design) the number of villages you may nuke are limited but probably sufficient for a small scale war.
Alf

[FDibbins]

MasterSplinter welcome to the forum

1. I have removed your email, it is never a good idea to post that kind of info on a public forum.
2. While the majority of the forum is indeed free, we do also have a Commercial Services forum where, for a fee, you can get attention from our top level gurus

[MasterSplinter]

Thank you for the replies! I will check out your link Alf! As for the email, I have an email-account that I only use for the game so I am not too concerned about spam. I got contacted by a guy who wanted to give it a shot, but thank you for informing me about your Commercial Services anyways!