+ Reply to Thread
Results 1 to 22 of 22

find the closest match to given latitude / longitude from data stored in 2 columns

  1. #1
    Registered User
    Join Date
    08-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    30

    Red face find the closest match to given latitude / longitude from data stored in 2 columns

    Greetings,

    I've run into a wall with this one, so I had to ask for help

    I have 2 different sets of data: temperature data recorded at meteo-stations defined by latitude / longitude, and major world cities also defined by latitude / longitude.

    After the user chooses the station's coordinates, temperature data is pulled out from the first data set; this works!
    However, I would like to be able to provide information about the closest city to the chosen meteo-station from the second data set. This doesn't work, and I don't have a clue how to go about it.

    Any good Samaritans out there? Please?
    File attached with sample data sets.

    Many thanks,
    Florin
    Attached Files Attached Files

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,964

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    Suggest you use the Haversine formula to calculate the length of the arcs of the great circle defined by the two positions and then find the minimum.

    Have a look at http://www.movable-type.co.uk/scripts/latlong.html
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  3. #3
    Registered User
    Join Date
    08-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    Hi Martin,

    Thanks for your reply.
    I knew about Haversine or spherical cos law, but do you suggest doing it about 1000 times for all cities in the data set?
    Isn't it going to be slow?

    Florin

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,637

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    How else would you find the minimum distance?
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    08-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    Matching the closest pair of coordinates from the cities data set?

  6. #6
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007
    Posts
    264

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    see attachment and run macro "AllStations"
    Attached Files Attached Files
    Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

  7. #7
    Forum Contributor
    Join Date
    12-14-2013
    Location
    Tilburg, Nederland
    MS-Off Ver
    Excel 2010
    Posts
    256

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    Not sure about what your trying to set in B10
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    Thanks bsalv, I'll check it out and let you know how it works.

  9. #9
    Registered User
    Join Date
    08-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    Nothing, this was just a debug output for one of my attempts.

  10. #10
    Registered User
    Join Date
    08-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    Hi Gerard, thanks for your try but your solution matches only the closest latitude while ignoring the longitude; it means you get the wrong city.

  11. #11
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007
    Posts
    264

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    Nothing, this was just a debug output for one of my attempts.
    Did you try to run that macro ?
    It 'll take probably 15 minutes to finish but afterwarts you get the last 4 columns in "Stations Data".
    You can see the progress of the macro in the statusbar.

  12. #12
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,637

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    This would just be a one-time exercise, would it not? Just calculate the nearest city to each of the stations.

  13. #13
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007
    Posts
    264

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    sorry for the error in the macro,
    see the result in attachment, sheet "Stations_Data"
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    08-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    Hi bsalv,
    I've tried to run your macro for all my 5700 stations but got the run-time error '9' subscript out of range after 747 stations at the line >> If MinCity = "" Then MinCity = c(i1, 3).
    Any suggestions?

  15. #15
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,637

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    Here are the nearest cities pre-calculated.
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007
    Posts
    264

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    that was the error in the previous macro in #6, that i1 must be i2, now see #13 or shg.
    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    08-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    Greetings,

    Let me summarise and give some thanks where due, before closing this thread.
    What I had in mind was a run-time solution for finding the closest city similar with Gerard's attempt, i.e. using match, index, offset, etc.; that's why I initially questioned mrice's proposal for computing the distance.
    What mrice, shg and bsalv proposed was a data set revamp, a new data set containing all info including the stations-cities closest match. On a balance I accept that this is the right solution and thank you all for helping. Reputation added.
    Have a good day.

  18. #18
    Registered User
    Join Date
    08-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    Hi shg, special thanks due to you for your very neat and fast distance calculation code.

  19. #19
    Registered User
    Join Date
    08-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    Oops! Sorry to pester shg, but I'm getting the wrong results when running your code for all my stations; see example in the attached jpg, the highlighted station is very close to Melbourne, Australia.
    Could you help please?

  20. #20
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,637

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    Maybe, but Melbourne is not in the list of cities. Nor is any other city in Australia.

  21. #21
    Registered User
    Join Date
    08-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    Time to wash my face; got egg all over it!
    Forgotten to add my full list o cities - in your file was only a sample. Apology!

  22. #22
    Registered User
    Join Date
    10-23-2017
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    2

    Re: find the closest match to given latitude / longitude from data stored in 2 columns

    Quote Originally Posted by florin_excel View Post
    Time to wash my face; got egg all over it!
    Forgotten to add my full list o cities - in your file was only a sample. Apology!
    Wondering why it returns the last data sets?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Distance between latitude and longitude
    By fazna ali in forum Excel General
    Replies: 2
    Last Post: 11-06-2012, 05:03 AM
  2. Replies: 7
    Last Post: 05-02-2012, 01:00 AM
  3. Latitude & Longitude processing help please
    By Pilot_Greg in forum Excel General
    Replies: 11
    Last Post: 12-14-2009, 03:31 PM
  4. [SOLVED] formula for longitude/latitude
    By Leo in forum Excel General
    Replies: 2
    Last Post: 06-03-2005, 02:05 PM
  5. Help with Latitude and Longitude.
    By LadiFireBug in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-22-2005, 05:09 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1