Solved

Posted on 2009-12-17

We have a client that has two Excel databases. One contains approximately 2500 names and addresses of health clubs. The other is a mailing list to their clients with approximately 20,000 records. They want to mail a postcard to the mailing list that lists the 3 closest healtclubs from the first list. Printing the variable data once we have it is a non-issue. What we need is one database (preferably in excel) that has the original mailing list, with the 3 closest health club locations on the same row as each mail recipient. Does anyone know of any software package or other extremely non-technical friendly type program that could do this? Thanks!

8 Comments

Thanks for your help Simon! Please find a sample attached.

HlthClubSample.xls

HlthClubSample.xls

See attached screenshot and workbook (yours, returned modified). I copied the club geocodes to make 19 fake customer records, so you'll see a diagonal line of zeros down the distance matrix.

I couldn't find my previous ASP code that I'd done this in, so I found some code at http://www.zipcodeworld.co

Usage is =distance(lat1 as double,lon1 as double,lat2 as double,lon2 as double,units as string)

units are "M" = Miles, "K" = Kilometres, "N"= Nautical miles

lat1 and lon1 are the coordinates for the customer, lat2 and lon2 are the coordinates for the health club

I've used it on the worksheet to calculate the distance from the customer to each club, then used another set of columns to rank the results and finally indirect(match(.... functions to get the names of the top 3 closest clubs.

NB becauase of the way the rankig works you may not currently ALWAYS get three matches (e.g. if two clubs are tied in 2nd place there will be no 3rd ranked club - the ranking would be 1,2,2,4 so the match(3... function would fail. That said, as the calculated distance is a double, it is a fairly remote possibility.

There may be better ways of doing the worksheet. This is working template though.

The worksheet formulae work if you paste new customers in then fill the formulae down.

To put in the full set of health clubs at the top, insert columns as necessary at the right end of the Health club columns and a 2nd set at the right of the health club rankings. If you want full health club address in the lookups, set the title row over the ranking columns to the full address rather than just the health club name.

Let me know if you have any problems

```
':::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
'::: :::
'::: this routine calculates the distance between two points (given the :::
'::: latitude/longitude of those points). it is being used to calculate :::
'::: the distance between two zip codes or postal codes using our :::
'::: zipcodeworld(tm) and postalcodeworld(tm) products. :::
'::: :::
'::: definitions: :::
'::: south latitudes are negative, east longitudes are positive :::
'::: :::
'::: passed to function: :::
'::: lat1, lon1 = latitude and longitude of point 1 (in decimal degrees) :::
'::: lat2, lon2 = latitude and longitude of point 2 (in decimal degrees) :::
'::: unit = the unit you desire for results :::
'::: where: 'm' is statute miles :::
'::: 'k' is kilometers (default) :::
'::: 'n' is nautical miles :::
'::: :::
'::: united states zip code/ canadian postal code databases with latitude :::
'::: & longitude are available at http://www.zipcodeworld.com :::
'::: :::
'::: For enquiries, please contact sales@zipcodeworld.com :::
'::: :::
'::: official web site: http://www.zipcodeworld.com :::
'::: :::
'::: hexa software development center © all rights reserved 2004-2005 :::
'::: :::
':::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Const pi = 3.14159265358979
Sub TestIt()
'MsgBox distance(32.9697, -96.80322, 29.46786, -98.53506, "m") & "miles"
'MsgBox distance(32.9697, -96.80322, 29.46786, -98.53506, "k") & " kilometers"
'MsgBox distance(32.9697, -96.80322, 29.46786, -98.53506, "n") & " nautical miles"
Dim inMiles
Dim msg As String
'inMiles = distance(32.9697, -96.80322, 29.46786, -98.53506, "m")
'MsgBox inMiles & " miles"
Debug.Print vbNewLine & "London To Coast"
inMiles = distance(51.5, 0.01, 51.074971, 1.172166, "m")
msg = Format(inMiles, "0.00") & " miles from London to the coast"
Debug.Print msg
'MsgBox msg
Debug.Print vbNewLine & "zero distance - shown as zero in worksheet on original code"
'Problem instance - returns VALUE error if used as worksheet function
inMiles = distance(33.096452, -96.691631, 33.096452, -96.691631, "m")
msg = Format(inMiles, "0.00") & " miles from from a place to itself"
Debug.Print msg
'MsgBox msg
Debug.Print vbNewLine & "zero distance - #VALUE error in worksheet on original code"
'Problem instance - returns VALUE error if used as worksheet function
inMiles = distance(30.380116, -97.67447, 30.380116, -97.67447, "m")
msg = Format(inMiles, "0.00") & " miles from from a place to itself"
Debug.Print msg
'MsgBox msg
End Sub
Public Function distance(lat1 As Double, lon1 As Double, lat2 As Double, lon2 As Double, unit As String)
On Error GoTo ErrorTrap
Dim theta
Dim dist As Double
theta = lon1 - lon2
dist = Sin(deg2rad(lat1)) * Sin(deg2rad(lat2)) + Cos(deg2rad(lat1)) * Cos(deg2rad(lat2)) * Cos(deg2rad(theta))
'19/12/09 - added conditional to prevent #VALUE errors for some zero distances
'Debug.Print "dist1=" & dist
'Debug.Print TypeName(dist)
If IsEmpty(dist) Then
dist = 0
Else
dist = acos(dist)
Debug.Print "acos(dist)=" & dist
dist = rad2deg(dist)
Debug.Print "rad2deg(dist)=" & dist
End If
distance = dist * 60 * 1.1515
Select Case UCase(unit)
Case "K"
distance = distance * 1.609344
Case "N"
distance = distance * 0.8684
End Select
CleanExit:
Exit Function
ErrorTrap:
Debug.Print Err.Number & " (" & Err.Description & ")" & vbTab '& Err.Line
Resume CleanExit
End Function
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
'::: this function get the arccos function from arctan function :::
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Function acos(rad)
'Debug.Print "Value of rad at start of acos(): " & rad
'Debug.Print "Value of Abs(rad)at start of acos(): " & Abs(rad)
If Abs(rad) <> 1 Then
On Error Resume Next
acos = pi / 2 - Atn(rad / Sqr(CDbl(1 - rad * rad)))
'19/12/09 - added inline errortrap due to error evaluating abs(rad)<>1 for values of rad very close to zero.
If Err.Number <> 0 Then
acos = 0
Err.Clear
End If
On Error GoTo 0
ElseIf rad = -1 Then
acos = pi
Else 'i.e. rad = +1
'provide for this eventuality
'acos = 0
End If
End Function
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
'::: this function converts decimal degrees to radians :::
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Function deg2rad(deg)
deg2rad = CDbl(deg * pi / 180)
End Function
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
'::: this function converts radians to decimal degrees :::
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Function rad2deg(rad)
rad2deg = CDbl(rad * 180 / pi)
End Function
```

HlthClubSample.pdfHlthClubSample.xls

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Advanced finding in Excel | 9 | 24 | |

In Excel 2010, how do I to use countif for a number of tabs listed on the first sheet of a spreadsheet? | 4 | 27 | |

IF OR formula Excel | 8 | 22 | |

Posting V12 | 2 | 16 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**12** Experts available now in Live!