# Thread: Extract informations if distance (lat,long) between two lists of places is lower than 50km Thanks: 0 Likes: 0

1. ## Extract informations if distance (lat,long) between two lists of places is lower than 50km

Hello,

I have two lists of places (in two Excel sheets) : cities and swimming pools.
I have the lat and long of all the places (for the cities it's the center).

I want to extract a list of all the pools nearby every cities.
So I would have a final list like this :
- Name of the city / CityLat / CityLong / Name of a pool closer than 50km
- Name of the city / CityLat / CityLong / Name of an other pool closer than 50km
- etc.

For now I have a formula (that seems accurate) to calculate the distance between two points :

But I have no clue how to adapt it to my needs.

I also try this VBA fonction that don't seems to work : http://www.codecodex.com/wiki/Calcul..._a_Globe#Excel

Can someone help me on this one.

Thanks  Reply With Quote

2. ## Re: Extract informations if distance (lat,long) between two lists of places is lower than 50km

Hi Matthieu,
if you want to get that list you basically need a massive cross table with e.g. all cities as rows and all pools as columns. What you could do is use column A,B,C for the city (starting at row 4, so the first goes in A4,B4,C4), lat, lon and row 1,2,3 for Pool, lat, lon (starting at column D, so the first goes in D1,D2,D3). In D4 your formula would be =ACOS(COS(RADIANS(90-D\$2)) *COS(RADIANS(90-\$B4)) +SIN(RADIANS(90-D\$2)) *SIN(RADIANS(90-\$B4)) *COS(RADIANS(D\$3-\$C4))) *6371
You can copy-paste that formula down/right.
Hope that works,
Koen  Reply With Quote

3. ## Re: Extract informations if distance (lat,long) between two lists of places is lower than 50km

this reminds me of some old threads

https://www.mrexcel.com/forum/excel-...-formulas.html

seems very similar. from what I recall, that query solution took seconds whereas formulas took 6 hours  Reply With Quote

4. ## Re: Extract informations if distance (lat,long) between two lists of places is lower than 50km

I did a distance calculation some time ago for zip codes in the US (the UK database of post code is too large for Excel).
My calculations were based on distance "as the crow flies" not as you drive (as shown in Google).
I have a sheet with zip codes and their corresponding Long and Lat values (approx 33000 of them).

Code:
```Function CentralAngle(ByVal lat1 As Double, ByVal lon1 As Double, _
ByVal lat2 As Double, ByVal lon2 As Double) As Double
' shg 2008-1111

' Returns central angle between two point in RADIANS
' using Vincenty formula

Const pi    As Double = 3.14159265358979
Const D2R   As Double = pi / 180#

Dim dLon    As Double
Dim x       As Double
Dim y       As Double

' convert angles from degrees to radians
lat1 = D2R * lat1
lon1 = D2R * lon1
lat2 = D2R * lat2
lon2 = D2R * lon2

dLon = lon2 - lon1  ' delta lon

x = Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(dLon)
y = Sqr((Cos(lat2) * Sin(dLon)) ^ 2 + (Cos(lat1) * Sin(lat2) - Sin(lat1) * Cos(lat2) * Cos(dLon)) ^ 2)
CentralAngle = WorksheetFunction.Atan2(x, y)
End Function```
was/is the UDF I used to calculate.  Reply With Quote

## User Tag List

50km, distance, extract a list, lat/long, lists 