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-22T04:58:32+00:00

Sorry - these are the correct details:

test.xlsx

Home

A1:AQ61

test.xlsx

PO Box

A1:AQ11

test.xlsx

Work

A1:AQ33

test.xlsx

Billing

A1:AQ118

Thanks again

HERE IS THE MODIFIED FORMULA:

IF(ISNA(VLOOKUP($A1,[test.xlsx]Home!A1:AQ61,1,FALSE))

IF(ISNA(VLOOKUP($A1,[test.xlsx]Billing!A1:AQ118,1,FALSE)),IF(ISNA(VLOOKUP($A1,[test.xlsx]Work!A1:AQ33,1,FALSE)),IF(ISNA(VLOOKUP($A1,[test.xlsx]POBox!A1:AQ11,1,FALSE)),VLOOKUP($A1,[test.xlsx]POBox!A1:AQ11,1,FALSE),"NO MATCH ANYWHERE"),VLOOKUP($A1,[test.xlsx]Work!A1:AQ33,1,FALSE)),VLOOKUP($A1,[test.xlsx]Billing!A1:AQ118,1,FALSE)),VLOOKUP($A1,[test.xlsx]Home!A1:AQ61,1,FALSE))

make sure to edit the formula according to my previous post

need to modify the ranges, put a $ before the ranges

example:

A1:AQ11 to $A$1:$AQ$11 on all the ranges

sorry

HERE:

IF(ISNA(VLOOKUP($A1,[test.xlsx]Home!$A$1:$AQ$61,$B$2,FALSE))

IF(ISNA(VLOOKUP($A1,[test.xlsx]Billing!$A$1:$AQ$118,$B$2,FALSE)),IF(ISNA(VLOOKUP($A1,[test.xlsx]Work!$A$1:$AQ$33,$B$2,FALSE)),IF(ISNA(VLOOKUP($A1,[test.xlsx]POBox!$A$1:$AQ$11,$B$2,FALSE)),VLOOKUP($A1,[test.xlsx]POBox!$A$1:$AQ$11,$B$2,FALSE),"NO MATCH ANYWHERE"),VLOOKUP($A1,[test.xlsx]Work!$A$1:$AQ$33,$B$2,FALSE)),VLOOKUP($A1,[test.xlsx]Billing!$A$1:$AQ$118,$B$2,FALSE)),VLOOKUP($A1,[test.xlsx]Home!$A$1:$AQ$61,$B$2,FALSE))
Was this answer helpful?

0 comments No comments
Anonymous

2017-10-22T04:54:19+00:00

Sorry - these are the correct details:

test.xlsx

Home

A1:AQ61

test.xlsx

PO Box

A1:AQ11

test.xlsx

Work

A1:AQ33

test.xlsx

Billing

A1:AQ118

Thanks again

HERE IS THE MODIFIED FORMULA:

IF(ISNA(VLOOKUP($A1,[test.xlsx]Home!A1:AQ61,1,FALSE))

IF(ISNA(VLOOKUP($A1,[test.xlsx]Billing!A1:AQ118,1,FALSE)),IF(ISNA(VLOOKUP($A1,[test.xlsx]Work!A1:AQ33,1,FALSE)),IF(ISNA(VLOOKUP($A1,[test.xlsx]POBox!A1:AQ11,1,FALSE)),VLOOKUP($A1,[test.xlsx]POBox!A1:AQ11,1,FALSE),"NO MATCH ANYWHERE"),VLOOKUP($A1,[test.xlsx]Work!A1:AQ33,1,FALSE)),VLOOKUP($A1,[test.xlsx]Billing!A1:AQ118,1,FALSE)),VLOOKUP($A1,[test.xlsx]Home!A1:AQ61,1,FALSE))

make sure to edit the formula according to my previous post

need to modify the ranges, put a $ before the ranges

example:

A1:AQ11 to $A$1:$AQ$11 on all the ranges

sorry
Was this answer helpful?

0 comments No comments
Anonymous

2017-10-22T04:47:39+00:00

Sorry - these are the correct details:

test.xlsx

Home

A1:AQ61

test.xlsx

PO Box

A1:AQ11

test.xlsx

Work

A1:AQ33

test.xlsx

Billing

A1:AQ118

Thanks again

HERE IS THE MODIFIED FORMULA:

IF(ISNA(VLOOKUP($A1,[test.xlsx]Home!A1:AQ61,1,FALSE))

IF(ISNA(VLOOKUP($A1,[test.xlsx]Billing!A1:AQ118,1,FALSE)),IF(ISNA(VLOOKUP($A1,[test.xlsx]Work!A1:AQ33,1,FALSE)),IF(ISNA(VLOOKUP($A1,[test.xlsx]POBox!A1:AQ11,1,FALSE)),VLOOKUP($A1,[test.xlsx]POBox!A1:AQ11,1,FALSE),"NO MATCH ANYWHERE"),VLOOKUP($A1,[test.xlsx]Work!A1:AQ33,1,FALSE)),VLOOKUP($A1,[test.xlsx]Billing!A1:AQ118,1,FALSE)),VLOOKUP($A1,[test.xlsx]Home!A1:AQ61,1,FALSE))

make sure to edit the formula according to my previous post
Was this answer helpful?

0 comments No comments
Anonymous

2017-10-22T04:45:01+00:00

Hi, I think the information below is what you were after:

test.xlsx

Home

A1:AS61

test.xlsx

PO Box

A1:AS11

test.xlsx

Work

A1:AS33

test.xlsx

Billing

A1:AS118

They're all in one file called test.xlsx and then it shows the name of the sheet and the range within the sheet.

As also mentioned, there is a fifth sheet called "names only" which contains all the names in column A.

Is that ok?

first make sure the POBox sheet has no space between them.

the names sheet heading put the numbers below them starting with the name at Column A as 1 all the way to Column AS.

HERE IS THE FORMULA:

IF(ISNA(VLOOKUP($A1,[test.xlsx]Home!$A1:$AS$61,1,FALSE))

IF(ISNA(VLOOKUP($A1,[test.xlsx]Billing!$A$1:$AS$118,1,FALSE)),IF(ISNA(VLOOKUP($A1,[test.xlsx]Work!$A$1:$AS$33,1,FALSE)),IF(ISNA(VLOOKUP($A1,[test.xlsx]POBox!$A$1:$AS$11,1,FALSE)),VLOOKUP($A1,[test.xlsx]POBox!$A$1:$AS$11,1,FALSE),"NO MATCH ANYWHERE"),VLOOKUP($A1,[test.xlsx]Work!$A$1:$AS$33,1,FALSE)),VLOOKUP($A1,[test.xlsx]Billing!$A$1:$AS$118,1,FALSE)),VLOOKUP($A1,[test.xlsx]Home!$A$1:$AS$61,1,FALSE))

EDIT the number 1 before the FALSE, replace it with the reference cell with the number above it.

so if you paste the formula in cell B3, replace the number 1 with $B$2

then test the first name to see how it behaves.

if it behaves accordingly, then copy it and paste it into the entire worksheet.
Was this answer helpful?

0 comments No comments
Anonymous

2017-10-22T04:42:26+00:00

Sorry - these are the correct details:

test.xlsx

Home

A1:AQ61

test.xlsx

PO Box

A1:AQ11

test.xlsx

Work

A1:AQ33

test.xlsx

Billing

A1:AQ118

Thanks again
Was this answer helpful?

0 comments No comments