Geocoding, proximity, radius, marketing, direct mail, variable data addresses, excel question???

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!
Who is Participating?
SimonConnect With a Mentor Commented:
Unable to get to work today due to snow, so here you go...

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, and tweaked it for excel. Your workbook now has one module with a distance function in it.
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               :::
':::                                                                         :::
':::  For enquiries, please contact                   :::
':::                                                                         :::
':::  official web site:                         :::
':::                                                                         :::
':::  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
    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
  Exit Function
  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
    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

Open in new window

JRowAuthor Commented:
PS: Our mail sort software has capability to geocode in terms of generating latitude and longitude, so we could provide the original lists with that information already incorporated.
If you already have numeric geocodes, it's a fairly simple arithmethic to find the sum of the difference in latitudes plus the difference in longitude. In Excel 2007 you have 16384 columns by 1048576 rows, so you could generate all the permutations on one worksheet and use lookup, match functions to get the closest 3 for each client.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

If you post a small sample of your geocoded addresses for the health clubs (Not the client data) it would help.
JRowAuthor Commented:
Thanks for your help Simon! Please find a sample attached.
I should also point out that the distances don't tally exactly with Google Earth or routemap software, but I don't think that matters as the error in distance calculation is proportional to the distance, so the rankings should still be valid. Please check against your own local knowledge before using in production. I think it underestimates all the distances by 5% or so.
Ping this thread. Was this solution of any use to you, JRow?
Thanks Teylyn.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.