Find Distance between different coordinates

Question

I have Location data (latitude and longitude) of 1000's of locations and need to compute the distance between each of them taken two combinations at a time.

Example:

Let's just say I have four location data (latitude and longitude data) and want to compute the distance between them

    Location      Latitude    Longitude

 1. New York(L1)  40.7128° N  74.0060° W
 2. Paris(L2)     48.8566° N   2.3522° E
 3. London(L3)    51.5074° N   0.1278° W
 4. Moscow(L4)    55.7558° N  37.6173° E

Need to calculate the distance between possible combinations i.e distance between L1&L2, L1&L3, L1&L4, L2&L3, L2&L4 and L3&L4

Excel Formula I'm using to compute distance is

=ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *6371

How can I calculate it for large data set say 100's or 1000's of locations?

Automate This Automate This · Accepted Answer · 2020-04-23T15:01:08

Alternatively, you can create a VBA function and then loop through your table.

Add this code to a Module in the VBA editor:

Public Function DistBetweenCoord(Lat1 As Double, Long1 As Double, Lat2 As Double, Long2 As Double)
    'Cell Formula
    'ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *6371

    With WorksheetFunction
        A = Cos(.Radians(90 - Lat1))
        B = Cos(.Radians(90 - Lat2))
        C = Sin(.Radians(90 - Lat1))
        D = Sin(.Radians(90 - Lat2))
        E = Cos(.Radians(Long1 - Long2))

        DistBetweenCoord = .Acos(A * B + C * D * E) * 6371
    End With
End Function

Now you can access this through code or in cell. Here is an example of in-cell:

=DistBetweenCoord(C1,D1,C2,D2)

Here is how to loop through all possible combinations in another Sub. Output is in immediate window.

Sub CalcAllDistances()
    With Worksheets("Sheet1")
        For i = 1 To 4
            For j = i To 4
                If i <> j Then
                    Debug.Print .Cells(i, 2) & " to " & .Cells(j, 2) & ": " & DistBetweenCoord(.Cells(i, 3), .Cells(i, 4), .Cells(j, 3), .Cells(j, 4))
                End If
            Next j
        Next i
    End With
End Sub

EDIT - To change output to Sheet2 try the following:

Sub CalcAllDistances()
    Dim wks_Output As Worksheet
    Set wks_Output = Worksheets("Sheet2")

    Dim OutputRow As Long: OutputRow = 1

    With Worksheets("Sheet1")
        For i = 1 To 4
            For j = i To 4
                If i <> j Then
                    wks_Output.Cells(OutputRow, 1).Value = .Cells(i, 2) & " to " & .Cells(j, 2)
                    wks_Output.Cells(OutputRow, 2).Value = DistBetweenCoord(.Cells(i, 3), .Cells(i, 4), .Cells(j, 3), .Cells(j, 4))
                    OutputRow = OutputRow + 1
                End If
            Next j
        Next i
    End With
End Sub

Find Distance between different coordinates

2 Answers