RUNNING THE PROGRAMS
Tools/Macro/Macros/Run
For each file there is only one macro available to be run,
namely, Address_matching_program.
Shortcut: Press and hold
down Ctrl and then m (Ctrl + m).
If the user has more than one file open and these files also
contain macros then Excel will have a choice of macros in the dialog
box. Macros in other files
will have (as a part of their name) the name of the file followed by an
exclamation mark (!) followed by the name of the macro.
3.
Excel is limited to 65,536 rows.
This means that a maximum of 65,535 observations can be processed
because the first row is used for headings.
4.
The programs have been tested using Excel 97 and 2000 for use in
the United States. The programs were written using Excel 2000.
No testing has been done using the programs with any foreign
language version. If you
want to use a foreign language version of Excel you should contact the
author first.
5.
Never save the four program
files under their original names, use Save As, and give the file a new
name (perhaps adding out or output to the file name to show that the
file contains output).
If you do save a program and then rerun it after clearing the
contents, the program might not correctly calculate the number of
observations in the new data set. The
most likely result is that it analyzes the same number of observations
that was in the file when it was saved.
Note:
Each of the four
programs has about 15 to 20 seconds of waiting time coded into the
program. The program will
appear to be on “hold” for these 2 to 4 second waits.
The waits were coded so that the user can see some of the
intermediate calculations and tasks done to achieve the final result.
OVERVIEW OF PROGRAMS
One_data_set
This program will find
address matches in a single data set.
Auditors could use the
program for the following:
1.
Address matches in a data set of vendor addresses.
2.
Address matches in a data set of Frequent Flyer mileage account
holders.
3.
Address matches in a data set of employee-designated accounts at
a bank.
4.
Address matches in a data set of passengers claiming for lost
baggage.
5.
Address matches in a data set of rebate claimants where the
rebate was limited to one-per-household.
6.
Address matches in a data set of insurance claimants.
7.
Address matches in a data set of taxpayers claiming a refund of
state taxes paid.
8.
Address matches in a data set of customers claiming under
warranty.
For the one data set
programs the Field labeled Code (Column B) is not processed in any way.
This field could be left blank or could contain any data – the
results will be unaffected.
One_data_set_ZipNoChange
This program will find address matches in a single data set just
like One_data_set with one major difference:
The data entered in
CityStateZip (Column J) will not be altered in any way.
It will be processed for matches “as is.”
This program is designed
for Canadian and British data where the Zip (postal) code contains both
letters and digits and each unique zip code is a small geographic area.
Here it would not be a good idea to reduce both (say) B3H 4Y7 and
M3G 4T7 to 347 by eliminating the digits.
For this program it would be best to extract only the
zip/postal codes and put them in CityStateZip.
The full city and state (province) name could be entered into
Other1 or Other2 (Columns C and D) as these fields are not altered in
any way during processing.
