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$3
5651="",""
,SQRT(($J2
-$J$2:$J$3
5651)^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.
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