Help me find the 3-10 closest locations

Hello there,

I have 35,000 locations. I want a formula that will find the closest 3 points from the same list. There was a prior question and solution here, but I cannot get it to work:

https://www.experts-exchange.com/questions/28561261/Find-10-closest-x-y-coordinates-between-two-huge-x-y-data-set.html

Here's my data:

Unique identifiers:
$A$2:$A$35651

Latitude:
$J$2:$J$35651

Longitude:
$K$2:$K$35651

Based on the question above I modified the formula that looked like a solution:

=SUM(IF(SQRT(($J2-$J$2:$J$35651)^2+($K2-$K$2:$K$35651)^2)=SMALL(IF($J$2:$J$35651="","",SQRT(($J2-$J$2:$J$35651)^2+($K2-$K$2:$K$35651)^2)),2),$N$2:$N$35651,0))

This returns a #NUM! error.

Nearby locations are in LM, LN, and LO2. I doubt we'd need to reference those, but some people used them in the event of a tie. Breaking a tie is a nice feature, but ties in my dataset will be so rare that it's more of a "nice to have" if you see an easy solution.

It looks like they're mostly using the SMALL(array,k) to get the second-closest value, which I understand. I can easily change the K and lookup range, or you can use RIGHT(LM1,1)+1 to get 2 since the column is called NearbyPlace1, NearbyPlace2, etc. And I assume we'll start with 2 because 1 would just re-find the current location.
Jospeh FAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
I think you will be well served if you calculate the geohash values for your 35000 points.  After hashing, you can sort the data by the geohash values and take the closest neighbors.
Here's a function that will return the geohash value based on the Lat/Lon parameters you pass to it.
Public Function GeoHash(ByVal parmLat, ByVal parmLon) as String
    Static oXMLHTTP As Object
    Dim strHTML As String
    
    If oXMLHTTP Is Nothing Then
        Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
    End If
    
    oXMLHTTP.Open "GET", "http://geohash.org/?q=" & parmLat & "," & parmLon & "&format=url&redirect=0", False
    oXMLHTTP.Send
    
    Do Until oXMLHTTP.ReadyState = 4
        DoEvents
    Loop
    
    If oXMLHTTP.Status = 200 Then
        strHTML = oXMLHTTP.responsetext
        GeoHash = Mid(strHTML, InStrRev(strHTML, "/") + 1)
    End If

End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TracyVBA DeveloperCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: aikimark (https:#a42442417)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

broomee9
Experts-Exchange Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.