How do you determine "closest" ? Are you comparing X with several other codes?
I think you need the Google Maps API for this.
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
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.
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]
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:
which is filled across and down.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)),"")
This function does the trigonometry:
This works for the United States...probably something could be adopted for the UK or elsewhere with Post Codes unlike the US.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
Just need the long. and lat. to make the calculations work.
It's only a link to another MrExcel thread, shouldn't get a 404
Maybe the full address will helpCode:https://www.mrexcel.com/forum/excel-questions/587230-excel-calculate-distance-miles-between-post-codes.html
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]
Like this thread? Share it with others