1

I have a excel sheet with 583 cities as origins and 8 cities as destinations. I have to find distance between each pair of these origins and destinations. Since, it will be a cumbersome task, is their a way I can input the origins and destinations from excel and get output as distance between the cities?

Frustrated Coder
  • 4,417
  • 10
  • 31
  • 34

2 Answers2

1

Check out my article on how to get geolocation parameters in Excel using Google services and calculate distances between addresses: http://www.analystcave.com/excel-calculate-distances-between-addresses/

AnalystCave.com
  • 4,884
  • 2
  • 22
  • 30
  • Hi, I think there must be a problem with your code or understanding on my part. As instructed, I pasted the code in a new module. Now, when I write =GetDistance(“Al. Jerozolimskie 2, Warsaw”; “Al. Jana Pawła II 38, Warsaw”) I get an error. Can you help out? – Frustrated Coder Aug 21 '14 at 12:09
  • I copied the code and it works e.g. "= GetDistance("New York, USA";"Chicago, USA")" gives me 1272 km. How are you connected to the Internet? You have to have a DIRECT internet connection not proxies etc. Share your error and the line of code which throws the error – AnalystCave.com Aug 21 '14 at 12:30
  • Yes, I am not connected to any proxies etc. Please see the links: http://tinyurl.com/m8a273m http://tinyurl.com/l3xzkxc – Frustrated Coder Aug 21 '14 at 15:59
  • Infact on putting a semicolon instead of a comma this is the error: http://tinyurl.com/oarjmme – Frustrated Coder Aug 21 '14 at 16:11
  • That was my second guess - locale :). Good luck! – AnalystCave.com Aug 21 '14 at 16:35
  • Location (country) specific formatting/settings. E.g. in my version of Excel by default I had ';' as a split between formula parameters, some people have ','. You can change locale in the Windows Control Panel (you will find the "list separator" setting in the Region and Language section in advanced settings). – AnalystCave.com Aug 21 '14 at 17:04
  • ok, but would it solve the problem? I mean does the choice of separator affect how the code is run? – Frustrated Coder Aug 21 '14 at 17:16
  • Check what kind of list separator you have in these settings and make sure it is ";". If the error persists please check if you copied the code correctly to your VBA project and made it Public (so it can appear in Excel as a formula) – AnalystCave.com Aug 21 '14 at 17:18
  • yes it does appear as a function in excel. I will check for this separator thing. Would you mind dropping ur email id so that I could contact u directly? – Frustrated Coder Aug 21 '14 at 17:20
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/59750/discussion-between-frustrated-coder-and-tkacprow). – Frustrated Coder Aug 21 '14 at 17:22
  • your code worked perfect except that if distance between 2 cities was >1000 km, it showed #VALUE! worked fine for all distances<1000km. Any guesses? – Frustrated Coder Aug 22 '14 at 02:42
  • Good to hear. Sorry do not know why. It works simply based on the Google API. Check the direct output from the Google API with your parameters maybe the problem is with the addresses not the macro. – AnalystCave.com Aug 22 '14 at 10:47
  • Hi, i tried your function, I got an error saying "Compile Error: Invalid outside procedure". I followed all the steps. Help Please. – Shobhan Taparia May 19 '17 at 10:30
1

I believe the easiest way would be to use Google Maps Distance Matrix API Web Services. There is an example URL request provided.

A request will look like this:

http://maps.googleapis.com/maps/api/distancematrix/json?origins=Vancouver+BC|Seattle&destinations=San+Francisco|Victoria+BC&mode=bicycling&language=fr-FR&key=API_KEY

You can replace the origins and destinations within the url with yours separated by pipes (|). It will take some work to copy them over manually. You might consider exporting the file as a .csv from Excel and using a scripting language to automate this process. See for example the urllib package of python.

Also note that as a free user, you will be limited on the number of origin and destination pairs you can put in one URL request.

josiekre
  • 795
  • 1
  • 7
  • 19
  • Yes, you are correct but i have like 500 origins and 13 destinations. I think I would need something more dynamic – Frustrated Coder Aug 21 '14 at 13:34
  • 2
    Honestly, I would take this process out of Excel. You'll have the most flexibility. It took me about 1 hour to get a python script up and running with `urllib` to do exactly what you want to do with the Distance Matrix API. Note that I have very limited experience with Python (close to none). – josiekre Aug 21 '14 at 22:44