# Create a 2D array that is populated by the distance between two locations

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.

For example:
Lat    Long
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

###### Who is Participating?

x
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.

Commented:
is this an Excel question?
0
Reservoir EngineerAuthor Commented:
yes
0
Commented:
Okay, attached is a workbook that includes a macro named BuildArray that you can execute to add a new sheet and populate with the distances.  Make sure you run this from the data sheet so it knows where the coordinate list is.

I think you may need to tweak the function that calculates the distances.  I implemented the formula you provided, but it seems a little different than some of the examples I found on the web.  For example:

EE29098228.xlsm

»bp
0
Commented:
Updated solution to cleanly handle the "0" diagonal values when coordinates are the same.

EE29098228.xlsm

»bp
0

Experts Exchange Solution brought to you by