Postcode distances

[keithobro]

Hello

At work I have an Excel worksheet containing the UK Postcodes of candidates.
We have 6 centres at which we run interviews. I want to calculate the
distances between a candidate's home and each of the 6 centres in order to
ascertain which is closest.

Does anyone know of a way of performing this calculation through Excel, or
of a piece of software that would achieve this, without massive cost?

Thanks.

Keith Brooker

 

[Tyro]

See this web site. It looks like the formula would be very easy to put into
Excel
http://www.pjenkins.co.uk/blog/index.php/2007/04/04/uk_post_code_distance_calculation/

Tyro

 

[Ron@Buy]

Alternatively you could introduce a link to a "Map" website (e.g.
http://www.streetmap.co.uk/) and add a link into your worksheet.

 

[JP]

For some reason I can't view the original message.

You could do this with a worksheet function, for example:

=HYPERLINK("http://www.mapquest.com/directions/main.adp?
go=1&do=nw&1a="&$C4&"&1c="&$D4&"&1s="&$E4&"&1z="&$F4&"&2a="&$J4&"&2c="&
$K4&"&2s="&$L4&"&2z="&$M4&"&1y=US&2y=US&cid=lfddlink","Driving
Directions & Distance")

Put the starting address in C4:F4 and destination address in J4:M4.

C4: Street Address
D4: City
E4: State
F4: Zip

J4: Street Address
K4: City
L4: State
M4: Zip

You have to adjust the formula since you are in the UK, and you have
to search each one manually, but you get the idea.

For a VBA solution, check out http://www.codeforexcelandoutlook.com/excel.html,
I wrote a function called GetDistance() which automates the above
process (somewhat).


HTH,
JP

 

[Pete_UK]

Chip Pearson has a formula for Great Circle distances here:

http://www.cpearson.com/excel/latlong.htm

Hope this helps.

Pete