• 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.

how to retrieve the closest person (or the shortest distance) in Excel?

Y

Yoni Golden

New Member
Hello
I have project I have to match between Hosts and Guest.
Every person in the worksheet has an address.
How can I calculate the distance between to addresses and retrieve for every guest his closest host.

Attaching a file for example, but I can make some changes.

P.S. I use Office 2010 and office365.
P.S.S. all names and addresses will be in Hebrew.
 

Attachments

  • Host and Guest.xlsx
    10.5 KB · Views: 6
Not possible with information provided. You'd need to somehow obtain Geo-coordinate (lat & long) for each address.

Then you can calculate distance using Haversine formula, or just use coordinate as is to find which host is closest.

Normally I'd obtain Geo-coordinate based on Zip/Postal code in North America (there are numerous databases out there for it).

If you only have address as starting point, you may want to look at Google API to obtain Geo-coordinate(s).

See link for detail on using Google API.
http://analystcave.com/excel-get-geolocation-coordinates-of-an-address/
 
You must log in or register to reply here.
Back
Top