# 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?

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 http://www.zipcodeworld.com/samples/distance.vb.html, 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 http://www.zipcodeworld.com               :::
':::                                                                         :::
':::                                                                         :::
':::  official web site: http://www.zipcodeworld.com                         :::
':::                                                                         :::
':::                                                                         :::
':::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

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
'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
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    :::
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
On Error Resume Next
'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
acos = pi
'provide for this eventuality
'acos = 0
End If
End Function

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
':::  this function converts decimal degrees to radians             :::
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
deg2rad = CDbl(deg * pi / 180)
End Function

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
':::  this function converts radians to decimal degrees             :::
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
End Function
``````
HlthClubSample.pdf
HlthClubSample.xls
0

Author 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.
0

Commented:
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.
0

Commented:
If you post a small sample of your geocoded addresses for the health clubs (Not the client data) it would help.
0

Author Commented:
HlthClubSample.xls
0

Commented:
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.
0

Commented:
0

Commented:
Thanks Teylyn.
0
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.