De-duping a mailing list

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

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.

Ok thanks - I'll give it a try.

Much appreciated.

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.

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, I am going to make certain assumptions.

1. workbooks, Home, Billing, Work, and PO Box sheets have Names and complete addresses in them

2 the names in all the sheets are in the first column

if you can show screenshots of these it would be easier to see their geography as opposed to using my imagination.

Hi,

In my second post I showed all the names of the columns from A to AQ. As you can see from that list, the fullname is in Column F.

The worksheets called Home, Billing, Work, and PO Box contain all those columns from A to AQ and the fullname is in Column F. This is because after I had filtered the columns I just copied and pasted them into the new worksheets.

The other worksheet called "names only" has the list of names in column A - there's nothing else in that worksheet except the names which have been normalised.

So to summarise, the fullnames are in column F in worksheets Home, Billing, Work, and PO Box and in column A in the worksheet "names only".

Can you help further to get the vlookup working?

for each workbook, I will need the:

workbook name

sheet name

range, i.e. A1:AR7

so I will know how to reference it into the formula.

example:

VLOOKUP($A1,[Home.xlsx]Sheet1!A1:AR100,1,FALSE)

THE REAL FORMULA I AM CREATING LOOKS SOMETHING LIKE THIS:

IF(ISNA(VLOOKUP($A1,[Home.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE))
IF(ISNA(VLOOKUP($A1,[Billing.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),IF(ISNA(VLOOKUP($A1,[Work.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),IF(ISNA(VLOOKUP($A1,[POBox.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),VLOOKUP($A1,[POBox.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE),"NO MATCH ANYWHERE"),VLOOKUP($A1,[Work.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),VLOOKUP($A1,[Billing.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),VLOOKUP($A1,[Home.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE))

for each workbook, I will need the:

workbook name

sheet name

range, i.e. A1:AR7

so I will know how to reference it into the formula.

example:

VLOOKUP($A1,[Home.xlsx]Sheet1!A1:AR100,1,FALSE)

THE REAL FORMULA I AM CREATING LOOKS SOMETHING LIKE THIS:

IF(ISNA(VLOOKUP($A1,[Home.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE))
IF(ISNA(VLOOKUP($A1,[Billing.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),IF(ISNA(VLOOKUP($A1,[Work.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),IF(ISNA(VLOOKUP($A1,[POBox.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),VLOOKUP($A1,[POBox.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE),"NO MATCH ANYWHERE"),VLOOKUP($A1,[Work.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),VLOOKUP($A1,[Billing.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE)),VLOOKUP($A1,[Home.xlsx]Sheet1!THE ENTIRE RANGE,1,FALSE))

and that blank worksheet with the names in it we will be formatting into something that looks like this: