Avatar of Jospeh F
Jospeh F
 asked on

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.
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Tracy

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
aikimark

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Tracy

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck