Excel: Step 1 - Introduction

The AddressDoctor for Excel^® Add-In enhances Microsoft^® Excel^® by adding data cleansing functionality to Excel. By using the AddressDoctor Web Service the Add-In validates, corrects, and formats postal addresses from 240 countries and territories in real-time. It is not necessary to install and update postal reference data and other software. High quality results are saved directly to an Excel^® worksheet within minutes.

AddressDoctor for Excel^® integrates seamlessly with the Excel^® user interface and adds a new menu items. This manual lists system requirements and how to run the Add-In. A separate chapter is dedicated on the flexible billing system offered by AddressDoctor.

Excel: Step 2 - Installation

System requirements

• for Microsoft^® Excel^® 2000 or higher
• Runs on Windows^® 98, Windows NT^® 4.0, Windows^® 2000, Windows^® XP, Windows Vista^®

Restrictions

• AddressDoctor for Excel^® is subject to the same restrictions as Microsoft^® Excel^®. This especially applies to the maximum number of data records per worksheet (65.XXX ) and the available import and export formats.
• Analyzes and corrects incorrectly fielded data through parsing
• Identifies addresses from 240 countries and territories
• Provides a properly formatted data output for all countries of the world
• Processes up to 1 Mio. addresses per hour

Information for Software-Version

• You can find the version number of AddressDoctor for Excel^® if you press the button “help” at each stage in the application.

Installation

1. Preparation of the installation - unpack

To minimize the required storage space and therefore the time of transmission, you receive the AddressDoctor for Excel^® add-in as a zip file. Before you start the installation, you have to unpack the file. You can chose between two options here:

• Double click on the file symbol. A new window containing the AddressDoctor file will open up. Pull this file via mouse drop on your desktop.
• Apply the program to unpack the zip files and follow the instructions.

2. Installation start

• After successfully unpacking the files double click on the AddressDoctor file. This will start Microsoft^® Excel^®.
• According to the current security level you can start right away - in this case the menu is extended by one more option: You can see the option "AddressDoctor" on the right, right beside the question mark.

If you have chosen a high security level, the following reference will appear:

Please confirm "activate macros", to start the program.

3. Durable embedding in Microsoft® Excel® menu

• If you do not receive a security alert when you start AddressDoctor for Excel^®, the program is now embedded firmly in your menu.
• If you do receive a security alert, please confirm "activate macros" to start the program.

Excel: Step 3 - Licensing and registration

You may copy AddressDoctor for Excel^® as often as you would like and install it on any number of computers. To process data, however, a user account is required. If you do not yet have a user account you may obtain one online. When the Add-In is first run the following dialog will appear that you can also access via the menu item "User Account settings"…

Please enter your user ID and the password. If your company wishes to tally the usage for separate departments a department ID may also be entered. Each department needs to be setup online before use. Please note that each deparment may have its own password, so be sure to use the correct password. If you do not wish to use departments, please enter a '0' in the department ID field.

Your account data is stored on your PC and has to be entered only once.

Excel: Step 4 - Field mappings

Assignment with field names

• The field name in the first line of your worksheet will be recognized and displayed in "step 2 of 6":

Now choose a category in the pull down menus on the right, which resembles the content of your data field best. You can choose from the following possibilities:

Assignment without field names

• If your fields are not named or if you would like to assign the fields according to the number of the column (e.g. B. A, B,…), leave the field “first row contains field name” deactivated. You will see the following overview. Continue as described above.

Excel: Step 5 - Result target

Output format

1. Selection of the result target. Select this option in step 3 of 6:

• Extension of the existing worksheet: The data will be inserted into the open file that has to processed. The results will be inserted in a new column next to the source data. This ensures an easy comparison of input and output.
• Production of a new worksheet: The processed data will be issued in a new excel worksheet. Unprocessed data can be added to this worksheet depending on the options chosen (see next item). Consequently the output file can be forwarded to e.g. a letter shop without further formatting.

2. Options for address display

• Insert complete delivery address in output file: One line with street, name of the building, house number, PO box or any other combination is issued in one field of the output file and can directly be taken into a packing slip or other documents.
• Formatted address line: Especially for mailings address lines (street or PO ox, postal code/zip, and locality, country) will be output according to postal standards. These can be taken directly for e.g. personalized circulars
• Country specific locality designation: Exports a line, which contains the standard designation of the locality (e.g. New York NY 10007) in the destination country.
• Street name and number of the building in the same field: Provides a field, which contains street name, house number, building number if necessary.
• Take over company/organization and contact in "formatted address lines": Takes over the designation of the company or organization automatically to the formatted address lines.

Excel: Step 6 - Options

Insertion of country designations

By choosing the default country in the pull down menu in step 4 of 6 the output of country designations can be suppressed selectively. If the destination country e.g. UK is selected, no country designation will be added to UK addresses.

Formatting options

• Blank columns: To design the output file structured as clearly as possible, blank columns can be inserted automatically. IMPORTANT NOTE: You might receive an error message caused by these blank columns.
• The number of address lines: The number of output address lines can be limited in order to make the best use of the space available on the address tags or mailings.
• Conversion of umlauts: if you select the option „eliminate special characters“, international available letters and letter combinations will replace country specific special characters.
• Capitalization: AddressDoctor for Excel formats the address lines automatically according to your default. You can chose between capitalization, mixed application, and country specific application.

OH-Excel: Step 7 - Settings

Data Quality Report

Turn report creation on/off:

In step 4 of 6 you decide whether you want a report after the processing of data, which analysis the result, by activating the option "create report". The report will be inserted automatically as an additional worksheet to your excel folder. Call it up via the index tab on the bottom of the screen frame.

• Activate the display of status codes in the result file: The result report contains status codes for every processed data record, which provides a documentation of the address quality and the changes that have been made. You can find these status codes in the columns OUT_Status, OUT_ElementMatchStat and OUT_ElementResultStat.
• The meaning of status codes can be queried online here .