• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 32
  • Last Modified:

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

Thanks in advance
0
Conor Wassmuth
Asked:
Conor Wassmuth
  • 2
1 Solution
 
aikimarkCommented:
is this an Excel question?
0
 
Conor WassmuthReservoir EngineerAuthor Commented:
yes
0
 
Bill PrewCommented:
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
 
Bill PrewCommented:
Updated solution to cleanly handle the "0" diagonal values when coordinates are the same.

EE29098228.xlsm


»bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now