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

[MisterMatthieu]

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 :
=ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *6371

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/Calculate_Distance_Between_Two_Points_on_a_Globe#Excel

Can someone help me on this one.

Thanks

 

[Rijnsent]

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

 

[Fazza]

this reminds me of some old threads

https://www.mrexcel.com/forum/excel-questions/403690-whats-better-than-sum-if-array-formulas.html

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

 

[kweaver]

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).

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.