Distance between addresses

[Kevin8819]

Hi,

I am looking for a way to find the closest destination based on a post code entered by an user. As below, when an user inputs "Post code A", I would like the macro to use maps and bring back the closest location to post code A which for e.g may be Post code Y


Post code entered : Post code A

List of Possible matches:
Post code X
Post code Y
Post code Z

Expected result

Closest Location: Post code Y

Any pointers will be great!

Thanks
Kevin

 

[kweaver]

How do you determine "closest" ? Are you comparing X with several other codes?
I think you need the Google Maps API for this.

 

[Michael M]

Been tried before....Have a look here....
https://www.mrexcel.com/forum/excel...culate-distance-miles-between-post-codes.html

 

[kweaver]

I found this app that I did years ago for a client. Maybe this function and info will help.

A column (starting in A3) has zips "from" and row 2 has zips "to" (starting in B3).

B3, for example, has this code:

=IFERROR(3960*CentralAngle(VLOOKUP($A3,zipinfo!$C$2:$E$33248,2,FALSE),VLOOKUP($A3,zipinfo!$C$2:$E$33248,3,FALSE),VLOOKUP(B$2,zipinfo!$C$2:$E$33248,2,FALSE),VLOOKUP(B$2,zipinfo!$C$2:$E$33248,3,FALSE)),"")

which is filled across and down.

This function does the trigonometry:

Function CentralAngle(ByVal lat1 As Double, ByVal lon1 As Double, _
                      ByVal lat2 As Double, ByVal lon2 As Double) As Double
    ' shg 2008-1111
    
    ' Returns central angle between two point in RADIANS
    ' using Vincenty formula

    Const pi    As Double = 3.14159265358979
    Const D2R   As Double = pi / 180#

    Dim dLon    As Double
    Dim x       As Double
    Dim y       As Double

    ' convert angles from degrees to radians
    lat1 = D2R * lat1
    lon1 = D2R * lon1
    lat2 = D2R * lat2
    lon2 = D2R * lon2

    dLon = lon2 - lon1  ' delta lon

    x = Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(dLon)
    y = Sqr((Cos(lat2) * Sin(dLon)) ^ 2 + (Cos(lat1) * Sin(lat2) - Sin(lat1) * Cos(lat2) * Cos(dLon)) ^ 2)
    CentralAngle = WorksheetFunction.Atan2(x, y)
End Function

This works for the United States...probably something could be adopted for the UK or elsewhere with Post Codes unlike the US.

Just need the long. and lat. to make the calculations work.

 

[Kevin8819]

I found this app that I did years ago for a client. Maybe this function and info will help.

A column (starting in A3) has zips "from" and row 2 has zips "to" (starting in B3).

B3, for example, has this code:

=IFERROR(3960*CentralAngle(VLOOKUP($A3,zipinfo!$C$2:$E$33248,2,FALSE),VLOOKUP($A3,zipinfo!$C$2:$E$33248,3,FALSE),VLOOKUP(B$2,zipinfo!$C$2:$E$33248,2,FALSE),VLOOKUP(B$2,zipinfo!$C$2:$E$33248,3,FALSE)),"")

which is filled across and down.

This function does the trigonometry:

Function CentralAngle(ByVal lat1 As Double, ByVal lon1 As Double, _
                      ByVal lat2 As Double, ByVal lon2 As Double) As Double
    ' shg 2008-1111
    
    ' Returns central angle between two point in RADIANS
    ' using Vincenty formula

    Const pi    As Double = 3.14159265358979
    Const D2R   As Double = pi / 180#

    Dim dLon    As Double
    Dim x       As Double
    Dim y       As Double

    ' convert angles from degrees to radians
    lat1 = D2R * lat1
    lon1 = D2R * lon1
    lat2 = D2R * lat2
    lon2 = D2R * lon2

    dLon = lon2 - lon1  ' delta lon

    x = Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(dLon)
    y = Sqr((Cos(lat2) * Sin(dLon)) ^ 2 + (Cos(lat1) * Sin(lat2) - Sin(lat1) * Cos(lat2) * Cos(dLon)) ^ 2)
    CentralAngle = WorksheetFunction.Atan2(x, y)
End Function

This works for the United States...probably something could be adopted for the UK or elsewhere with Post Codes unlike the US.

Just need the long. and lat. to make the calculations work.

Thanks for this. I will try this one and come back to you. Also, the link that you shared in the PM takes me to a 404 page. (Sorry, I am not able to DM you)

 

[Kevin8819]

How do you determine "closest" ? Are you comparing X with several other codes?
I think you need the Google Maps API for this.

Comparing (1)"Post code A" -(Dynamic) with a list of (2)Static post codes to arrive at the shortest distance between (1) and (2)

 

[Michael M]

It's only a link to another MrExcel thread, shouldn't get a 404

https://www.mrexcel.com/forum/excel-questions/587230-excel-calculate-distance-miles-between-post-codes.html

Maybe the full address will help