De-duping a mailing list

Anonymous

Hi everyone,

I have a spreadsheet consisting of about 150 rows. It contains the full names of the people in Column F and each of the rows have either a “billing address”, “home address”, “work address” or “PO address”. The spreadsheet is sorted by column F (Full name) in alphabetical order so that you can clearly see the duplicate names. In fact, the duplicates in column F were identified in another workbook and copied into the current separate one so that duplicate customer records could be removed in preparation for a mailout.

In most instances there are only two duplicates for any particular customer but in some cases there could be 3, 4 or even 5. The goal of the exercise is to keep the customer row that has a home address as that is the preferable address to send to. If there’s no home address, then keep the billing address. If no billing address, then keep the work address. If no work address keep the PO box address.

My first attempt at this de-duping was to sort by home address, then by billing address, then by work address, then by PO box address. Even though this helped identify which entries had a home address, it still hasn’t solved the problem as it hasn’t de-duped as I would like and as I described in the previous paragraph.

Can anyone tell me if this can be done in excel and if yes, how I would go about it? Or would I would need to get some vba code written?

Would really appreciate any advice.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

48 answers

Anonymous

2017-10-22T02:36:15+00:00
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.
Was this answer helpful?

0 comments No comments
Anonymous

2017-10-22T03:08:53+00:00

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?
Was this answer helpful?

0 comments No comments
Anonymous

2017-10-22T03:30:16+00:00

Also, I forgot to add that if it's easier to do the vLookup, I can move the fullname from column F to column A in the worksheets called Home, Billing, Work, and PO Box.
Was this answer helpful?

0 comments No comments
Anonymous

2017-10-22T04:10:17+00:00

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))
Was this answer helpful?

0 comments No comments
Anonymous

2017-10-22T04:14:33+00:00

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:
Was this answer helpful?

0 comments No comments