Calculate closest distance among multiple points.

[vicpinto1970]

I have a data set with thousands of rows of unique locations that include a LAT and LON. For each row, I would like to calculate the closest location in relation to all the other locations and store this data in a new set in the form of LOCATION - CLOSEST LCOATION - DISTANCE. I created some VBA code to do this but the problem is my data set contains thousands of rows and it takes the code about 11 minutes to run. Too slow.

I was wondering if a combination of power query and power pivot could speed things up.

Any thought?

 

[DRSteele]

Welcome to the Forum.

I realise you asked for a PQ solution, but this kind of thing can be done in Excel with formulas. Check out this post in this Forum to see if it helps at all. There are dozens of other posts here if you search for keyword "longitude".



The formula to calculate distances between two locations on a sphere is key to it all. It's called the Grate Circle Distance.

Great-circle distance - Wikipedia
BlueMM: Excel formula to calculate distance between 2 latitude, longitude (lat/lon) points (GPS positions)
Latitude And Longitude