Two_data_sets
This program will find
address matches between two data sets.
Auditors could use the
program for the following:
1.
Address matches between a data set of vendor addresses and
employee addresses.
2.
Address matches between a data set of Frequent Flyer mileage
account holders and employees (where the account has accumulated a high
mileage balance).
3.
Address matches between a data set of employee-designated
accounts at a bank and nonemployee-designated accounts.
4.
Address matches between a data set of passengers claiming lost
baggage and employee addresses.
5.
Address matches between a data set of rebate claimants and
employees.
6.
Address matches between a data set of insurance claimants and
employee addresses.
7.
Address matches in a data set of taxpayers claiming a refund of
state taxes paid and state employee addresses.
8.
Address matches in a data set of customers claiming under
warranty and employee addresses.
For the two data sets
programs the Field labeled Code (Column B) must be coded with the
number 0 (the number zero) for one data set and the number 1 (one) for
the second data set. The
tests data set has the addresses coded 0 and 1 as an example.
Note:
The program will only return a match where an address in data set 0
matches an address in data set 1. If
there are matches in data set 0 (or 1) but no corresponding match in the
second data set, no match will be shown.
Two_data_sets_ZipNoChange
The data entered in
CityStateZip 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
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.
DATA INPUTS
Ref # (A)
This field is intended for
a reference number such as an employee number or a vendor number.
The programs will execute without any information in this field.
Code (B)
This
field is not used by the One Data Set programs and these programs will
ignore any data in this field.
For
the Two Data Sets programs this field must be coded with a 0
(zero) for one data set and a 1 (one) for the second data set.
Other 1 (C)
This field allows for any
descriptive data regarding the address being matched (perhaps total
dollars paid). For the
ZipNoChange Programs this field could contain city or state information. This field is not processed and could therefore also be left
blank.
Other 2 (D)
This field allows for any
descriptive data regarding the address being matched. For the ZipNoChange Programs this field could contain city or
state information. This
field is not processed and could therefore also be left blank.
Name (E)
This field allows for a
name (employee, vendor, claimant) for the observation being tested.
This field is not processed and could therefore also be left
blank.
Address 1 (F)
This field is the first line of the address, such as, 123 Ashton
Street. This field must
contain an entry. The
program counts the entries in this field and this number is used to
determine the row number that corresponds to the last observation in the
data set(s). If there are
blanks in this field then the programs will not copy the formulas all
the way to the last observation. Users
should insert characters (e.g., “none”) into those Address 1 rows
where that field is blank. By
sorting the data set on Address 1 the blanks will be at the top or the
bottom of the data set, depending on whether the sort was ascending or
descending.
Address 2 (G)
This field is the second line of the address (such as suite
number or attention of). This
field could be left blank.
Address 3 (H)
This field is the second line of the address (such as suite
number or attention of). This
field could be left blank.
CityStateZip (J)
This field is the last line of the address (such as Cincinnati,
Ohio, 45201). It will be
reduced to five digits unless the ZipNoChange programs are used.
