In excel how can a calculate distances between 2 large sets of points with lat/longs?

Greetings everyone, I have 2 sets of points in excel (group A and B), each set has around 1000 entries that contain lat/long. What I want to do is compare Group A against Gro开发者_JAVA百科up B and figure out for every point in Group A what point in Group B is the closest. What would be the easiest way to do this in excel?

One solution I started working on is a macro that will compare the 2 points against each other.

    Sub distance()
'
' distance Macro
'
' Keyboard Shortcut: Ctrl+d
'
    Range("D4:E4").Select
    Selection.Copy
    Sheets("Analog Data").Select
    Range("F3:G3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D3:E3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Cluster 58").Select
    Range("FT4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D5:E5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Analog Data").Select
    Range("F3:G3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D3:E3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Cluster 58").Select
    Range("FT5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Now how can I tell visual basic to set the ranges to go to the next one down so I do not have to type each individual one all the way down to Range("D1237:E1237")?

You can use this formula to find the Min of the differences, you just may need to create your own way to determine difference in lat/long (like a UDF). Put this formula in another column, and drag down:

=SUMPRODUCT(ABS(MIN(A1-B:B)))

The question inside the body of your post (about the code) is completely different from the question in the title of your post.

Now I'm going to answer the former very literally, with the caveat that this is a really bad way to do things. You're asking for something like this:

Sub distance()

Dim i As Long

For i = 0 To 1236

    Range("D4:E4").Offset(i, 0).Copy
    Sheets("Analog Data").Select
    Range("F3:G3").Offset(i, 0).PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("D3:E3").Offset(i, 0).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Cluster 58").Select
    Range("FT4").Offset(i, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Next i

End Sub

Cleaning up this awful mess up a bit leads to this single statement, which does exactly the same thing as what your million-line code does:

    Sheets("Analog Data").Range("F3:G3").Resize(1237, 2).Value _
        = Sheets("Cluster 58").Range("D4:E4").Resize(1237, 2).Value

Now onto the lat-long thing. You're aware, I presume, that with (lat,long) given in degrees, points (0,0) and (0,0.1) are roughly 10 km apart, while points (90,-131) and (90,74) are exactly 0 km apart? This is assuming a spherical Earth. Assuming a more realistic datum e.g. WGS84 leads to more complicated distance calculations.

Before asking how to do this in Excel, you should ask yourself (and tell us) how you would do this on a piece of paper.

About the lat-long thing, you should understand how to convert lat/long into false north-east or UTM coordinates. This page can help you in this matter, but is in spanish, beside aditional issues arises if you need to determine the distance between diferent time zones or hemispheres of the earth.

About the programming part and almost macro-free solution would be:

(assume everything in UTM coordinates and in the same time zone and same hemisphere)

• Reserve 3 cells to the following data :

ID_Point_in_GroupA

NORTH_of_ID

EAST_of_ID

• NORTH_ID and EAST_ID can be determinated adding a "index" function based in ID_Point_in_GroupA.
• Calculate the pythagorean distance of every point in a group B to NORTH_ID and EAST_ID
• Determine the closet.
• Determine ID of the closest point (a "match" function will help).
• Macro the looping troguht all the ID_Point_in_GroupA in gropu A and the storing of the results.

Regards

Here is the best formula I could come up, after some tedious search..

you can copy this directly to excel and update with your values..

Location-1  Latitude    Longitude   Location-2  Latitude    Longitude   Distance in Kilo Meters
POI-1   40.634068   -73.941248      POI-2       41.634068   -73.941248  

=ACOS((COS((B2*PI()/180))*COS((E2*PI()/180))*COS((-1*(F2*PI()/180))-(-1*(C2*PI()/180))))+(SIN((B2*PI()/180))*SIN((E2*PI()/180))))*6371