Distance between addresses

Kevin8819

New Member
Joined
Jun 17, 2019
Messages
3
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

Well-known Member
Joined
May 8, 2018
Messages
655
How do you determine "closest" ? Are you comparing X with several other codes?
I think you need the Google Maps API for this.
 
Last edited:

kweaver

Well-known Member
Joined
May 8, 2018
Messages
655
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:

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:

Code:
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

New Member
Joined
Jun 17, 2019
Messages
3
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:

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:

Code:
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

New Member
Joined
Jun 17, 2019
Messages
3
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

Well-known Member
Joined
Oct 27, 2005
Messages
17,955
Office Version
2013
Platform
Windows
It's only a link to another MrExcel thread, shouldn't get a 404

Code:
https://www.mrexcel.com/forum/excel-questions/587230-excel-calculate-distance-miles-between-post-codes.html
Maybe the full address will help
 
Last edited:

Forum statistics

Threads
1,077,618
Messages
5,335,267
Members
399,009
Latest member
twcaddell

Some videos you may like

This Week's Hot Topics

Top