• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Macro To Create Time Between Addresses

S

sealion1

Member
Hi all,

I have a large number of postcodes that I want to put into a spreadsheet and then be able to see how many I could go to in a certain time.

So for example, I would like to put in a box the number of hours e.g: 4 hours that I would need to drive and how many addresses I could go to in that time.

Is this possible?

Thanks in advance.
 
This is one of those problems that sounds like is should be easy, but is actually quite hard. Your question is a derivative of what is known as the "travelling salesman problem"
It's possible to solve, but a bit advanced for XL I think.

In your case, we'd need to calculate shorted distance so that we know how far we could drive to connect them all.

Now, if instead of multiple addresses, you just want to know which of the addresses you could get to in 4 hours, we could easily calculate that (given also a set speed). We'd need to just calculate distance between your location and the other locations, figure how far you cold travel in X hours, and then see which ones are possible.
 
Hi ,

It depends on many factors :

1. How many addresses are you likely to have ?

2. Do you have the distances between each and every address , or the time it will take to go from one to the other ?

3. Do you have some way of designating their relative orientations in terms of direction ?

Suppose there are just 3 cities A , B , C ; these can be laid out either in a straight line or in a triangular layout.

Even if they are in a straight line , their orientation can be :

A -> B -> C , A -> C -> B , B -> A -> C , B -> C -> A , C -> A -> B , C -> B -> A

which is 6 different orientations for a straight line layout.

You can imagine the possibilities when there are a dozen cities.

Narayan
 
Perhaps if you uploaded a sample of what your data looks like, and what type of results you want, that would help?
 
Hi all,

Apologies - I have been away with no internet access.

@Luke M - I will upload a file later on with sample data.

Thanks for all your help :)
 
You must log in or register to reply here.
Back
Top