Results 1 to 7 of 7

Thread: Distance between addresses
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Distance between addresses

    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

  2. #2
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    642
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Distance between addresses

    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 by kweaver; Jun 17th, 2019 at 03:01 AM.

  3. #3
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,882
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Distance between addresses

    Been tried before....Have a look here....
    https://www.mrexcel.com/forum/excel-...ost-codes.html
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  4. #4
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    642
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Distance between addresses

    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.

  5. #5
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Distance between addresses

    Quote Originally Posted by kweaver View Post
    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)

  6. #6
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Distance between addresses

    Quote Originally Posted by kweaver View Post
    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)

  7. #7
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,882
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Distance between addresses

    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 by Michael M; Jun 17th, 2019 at 08:51 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •