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?

Answer 1

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

enter image description here

---

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

enter image description here

• Unleash Speed: Getting Invalid Name error while applying "=DistBetweenCoord(C1,D1,C2,D2)" formula in cell
• Automate This: That probably means it can't find the function. Did you add the function into a Module or the worksheet code? It won't work if located in the worksheet code.
• Unleash Speed: Yes now formula working in cell can you elaborate on Sub CalcAllDistances part and how to get Immediate window
• Automate This: Sure, when in VBA editor (ALT+F11) you can show Immediate window by View->Immediate Window or by CTRL+G
• Unleash Speed: Thanks Works great but its limited to four location entries only right How can I extend it to say 100's or 1000's of entries?

Answer 2

I would use a matrix. Create a sheet (like 'GeocodeList' or something) for the geocodes, like your city|lat|lon in the question. Then create a sheet (like 'Distances') for a matrix, where the column and row labels are the city names. Then you can parameter your excel formula using V.LOOKUPs that look up exact codes from GeocodeList.

The formula would look like this (X is row number, Y is column letter.):

=ACOS(COS(RADIANS(90-VLOOKUP($A(X); GEOCODETABLE, LATCOLINDEX, 0)))
*COS(RADIANS(90-VLOOKUP((Y)1ドル; GEOCODETABLE; LATCOLINDEX, 0)))
+SIN(RADIANS(90-VLOOKUP($A(X); GEOCODETABLE, LATCOLINDEX, 0)))
*SIN(RADIANS(90-VLOOKUP((Y)1ドル; GEOCODETABLE; LATCOLINDEX, 0)))
*COS(RADIANS(VLOOKUP($A(X); GEOCODETABLE, LATCOLINDEX, 0)-VLOOKUP((Y)1ドル; GEOCODETABLE; LONCOLINDEX, 0))))
*6371

So basically the VLOOKUP automatically fetches your parameters, and you can extend the formula for the whole matrix.

• Unleash Speed: Created two sheets sheet1: GeocodeList- that contains city names as colum1 latitude Data as column2 longitude data as column3 sheet2: Distances- that contains city names from A2: A5 and B1: E1 didn't understand VLOOKUP formula can you elaborate it ?
• MarkO: VLOOKUP returns the value in the xth column of the lookup table, where the first column's value equals your lookup value. So when your vlookup looks like VLOOKUP($A2; 'GeocodeList'!$A:$C;2;0) the formula will return tha value in the 2nd column (col B) of sheet GeocodeList, where the first column in GeocodeList (col A) equals your lookup value (A2 on the Distances sheet) So if A2 in 'Distances' is 'New York', and on 'GeocodeList' A3 is 'New York', you will get New York's latitude (from 'GeocodeList' B3)
• MarkO: Note: the '0' at the end of the vlookup formula means it must find an exact match for the lookup value