I have a unique identifier (column A) with its respective set of coordinates (DD units, ex. 59, -110) for 500+ locations and would like to write a macro that creates a 2D array (500+ X 500+) and automatically populates each cell within the array with the distance between all of the other coordinates in the data set.
A 59 -110
B 34 -90
C 78 -80
Hopefully I can create an array that looks like this:
A B C
A 0 X Y
B X 0 Z
C Y Z 0
The formula to calculate the distance between the two coordinates is:
=ACOS( SIN(lat1*PI()/180)*SIN(lat2*PI()/180) + COS(lat1*PI()/180)*COS(lat2*PI()/180)*COS(long2*PI()/180-long1*PI()/180) ) * 6371000
In addition to this, if possible I would like to add a row onto the end of the array that gives the lowest distance calculated that is not zero.
It does not have to be a macro either, I just thought this may be the fastest most efficient way to accomplish this
Any help with this would be greatly appreciated
Thanks in advance