?
Solved

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

Posted on 2009-12-17
9
Medium Priority
?
685 Views
Last Modified: 2012-05-08
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!
0
Comment
Question by:JRow
  • 6
  • 2
8 Comments
 

Author Comment

by:JRow
ID: 26076581
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
 
LVL 18

Expert Comment

by:Simon
ID: 26076866
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
 
LVL 18

Expert Comment

by:Simon
ID: 26078701
If you post a small sample of your geocoded addresses for the health clubs (Not the client data) it would help.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:JRow
ID: 26080304
Thanks for your help Simon! Please find a sample attached.
HlthClubSample.xls
0
 
LVL 18

Accepted Solution

by:
Simon earned 2000 total points
ID: 26084756
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               :::
':::                                                                         :::
':::  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

Open in new window

HlthClubSample.pdf
HlthClubSample.xls
0
 
LVL 18

Expert Comment

by:Simon
ID: 26084840
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
 
LVL 18

Expert Comment

by:Simon
ID: 26407640
Ping this thread. Was this solution of any use to you, JRow?
0
 
LVL 18

Expert Comment

by:Simon
ID: 30597019
Thanks Teylyn.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

850 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question