De-duping a mailing list

Hi,

I was just going through your steps and have managed to filter each of the addresses and have copied each address set into a separate workbook, and I named each of these workbooks as follows:

Home

Billing

Work

PO Box

I then used the "Remove Duplicates" function in the data tab to "normalise" the fullnames (from column F) and copied them into another separate workbook called "names only". They're all located in column A of this workbook. You mentioned that it was in this workbook that I would put the VLookup function.

I know that the syntax for a VLookup is:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

If the first step is to use the VLookup function on the Home workbook, can you let me know what to include in the function? The "Home" workbook contains all the columns from columns A to column AQ and the "Fullname" is still in column F in the "Home" workbook. As mentioned, all the normalised names are in column A of the workbook called "names only" so can you tell me where to place the vlookup function in that workbook and what the function should contain?

Sorry but I've never used a VLookup before.

Ok thanks - I'll give it a try.

Much appreciated.

on the menu ribbon select the data tab, then select filter.

then on the home address column only show non blank cells.

then copy the records to another sheet.

then set the filter to show all, then move to filtering the next criteria and copy the filtered data into another sheet and so on until you have copied all records that contain complete records for each home address, billing address, work address, and PO Box address.

now you have four lookup tables for each criteria.

next normalize all the names where you only have one instance for each name and put those name in its own sheet, this sheet is where you will be putting your formula to lookup the name in the home address lookup table first if it exists, then so on and so forth until all records are normalized.

yes it is labor intensive, but once you have normalized this data, it can then be stored in a relational database (if this is a long term endeavor) where if you would ever need to do this exercise again all you would have to do is to create a query :)

if you had thousands of rows of data writing vba code would be better, but for 150 rows of data manually doing it is less of a headache even for someone who is an expert writing vba code.

Thanks for the reply, 

The following are the column names from column A to column AQ. Does that help with troubleshooting?

Date 1

Date 2

Membership Type

Secure zone Name

Title

Fullname

Firstname

Middle Name

Lastname

Email 1 (Primary)

Email Address (Alternate)

Username

Cell Phone

Profession

Double Opt-In Status

Opt-In Status

Billing Address

Billing Address City

Billing Address State

Billing Address Zipcode

Billing Address Country

Home Address

Home Address2

Home Address City

Home Address State

Home Address Zipcode

Home Address Country

Home Phone

Work Address

Work Address2

Work Address City

Work Address State

Work Address Zipcode

Work Address Country

Work Phone

Alternate Work Phone Number

PO Box Address

PO Box Suburb

PO Box State

PO Box Postcode

PO Box Country

Preferred Contact Method

Comments

it would help to find a solution if we can see the geography of you spreadsheet