We contacted our customer and found that if there were addresses  in the first two columns, it was to be used instead of the address in the last two columns.

I had to get all the addresses to be used in one set of columns. So I selected the whole first column of street addresses and ran the following macro:

Sub OneAddress()

‘This macro is to consolidate multiple addresses

For Each Cell In Selection

If Cell.Value > “.” Then Cell.Offset(0,2) = Cell.Value

If Cell.Value > “.” Then Cell.Offset(0,3).Value = Cell.Offset(0,1).Value

Next Cell

End Sub

That did the trick. Now I have one set of addresses (the second set) to import into my mailing software.

But, what is the criteria that I use to determine whether I make the corrections or send them back to the customer to be corrected? I use two factors for deciding: the nature of our business and the number of uncorrectable addresses.

One criteria I use is the nature of our business. We are a printing firm that uses mailing as a selling point to customers. Good service dictates that I correct the records and not charge the customer. I want to make the mailing service as inviting as possible. Contacting the customer over a few address corrections make mailing seem a burden rather than a service.

Another criteria is, “How many records need further attention for validity?” If there are too many, the customer needs to be contacted.  I consider this to be a service to the customer. The customer wants valid files. A lot of uncorrectable addresses means there is a problem in the way the customer is obtaining the addresses. Hopefully, the customer wants good addresses to start with and will take a second look at his process.

Consider the nature of your business and the number of uncorrectable addresses to determine whether you take extra steps to fix addresses in a customer’s file.

Our customer files all returned mail by address. If there is only one return for an address, they consider it inconsequential. They figure that something unusual happened like – there was a substitute mail carrier, it got sorted wrong at the post office, or something along those lines.

If several returns are in the file for one address, then our customer takes the time to see what needs to be done.

This seemed like a good plan to me

I have dome this process a few times before for this customer. This time there were 21 primary files to combine and 6 exclusion files for pulling records out.

When the mailing software I use could not finish the operation, I tried a few things. What finally worked showed that the problem was the size of the merge/purge the software was working with.

What worked was doing the merge/purge against the 6 exclusion files for only 7 of the primary files at a time. I clearly named each output file using “A”, “B”, and “C” at the end of the file names so I knew which was which.

Then I combined the three output files into one file and did a duplication search on the one file.

So, if you have a lot of files to work with in a merge/purge, you do not have to do the merge/purge on all files at once. You can break it down into a few files at a time. Just remember to clearly name each output file, to combine the output files into one file, and to dedupe the combined final file.

It happened to me yesterday. I exported a tab delimited file. I found that when a field length ran past the tab stop of the previous record, our inkjet device read two tab stops between the fields. Needless to say, that export did not work.

I changed the export to be a comma delimited file with a double-quote qualifier. This worked because, in a comma delimited file, the length of a field does not matter. The double-quote qualifier preserved the veracity of the fields that had a comma somewhere in the middle of the field.

This means that between now and May 2011 we have to start using Intelligent Mail barcodes.

Our plan is to test Intelligent Mail barcodes and to start using them sparingly, gradually increasing until May 2011. We expect this gradual use will allow us to work out the “kinks” a little at a time and make the transition a lot smoother.

So, one problem to look for is apostrophes in an email field.

Your mailing may not require one of the fields in your standard export file. Go ahead and use the standard export file. You do not have to include the unused field in the merge.

My standard export file includes the following fields:

• Bar code
• Endorsement Line
• Full Name
• Title
• Business
• Address 1
• Address 2
• City, State, Zip
• Country
• Sort Position
• Tray Number
• Break Mark

• Failed Move Update – a list of all addresses that failed NCOA processes.
• Failed Delivery Point Validation – a list of all addresses that could not be validated by the post office.
• Move Update – a list of all the addresses that returned a new address during the NCOA process.
• Passed Delivery Point Validation and Move Update – a list of all the addresses that are updated and verifiable by the post office.

Remember to include in the key fields in the lists, such as account number, to help the customer identify addresses.

I have found that this occurs when the data provider puts a non-printing character within a field in the middle of the file. For instance, a carriage return in a field will make the one record export as two records, half of the original in the first record and the other half in the second. The first field of the second record picks up where the first record left off.

The solution is to remove the nonprinting characters from the file before exporting.

I will use either Satori Software’s Bulk Mailer program or the Easy-XL addition to Microsoft Excel to take out nonprinting characters.