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-22T05:37:18+00:00

Ok I inserted the formula as follows in cell B3:

=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))

But I got the following error:

The formula you typed contains an error.

I noticed that in the title bar of my excel file it just says test, not test.xlsx - could this be contributing to the problem. Also, I'm using a Mac version of excel so not sure if that would have anything to do with it?
Was this answer helpful?

0 comments No comments
Anonymous

2017-10-22T05:29:33+00:00

Sorry - didn't see that. I just test it by first pasting the formula into B3 exactly as you have it but nothing happened, ie. the formula seemed to just be entered as text, not a formula. I then tried:

=VLOOKUP(

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))

)

But that didn't work either. Am I supposed to put your formula into B3 exactly as is?

No just paste the formula below without any modification:

=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-22T05:25:54+00:00

Sorry - didn't see that. I just test it by first pasting the formula into B3 exactly as you have it but nothing happened, ie. the formula seemed to just be entered as text, not a formula. I then tried:

=VLOOKUP(

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))

)

But that didn't work either. Am I supposed to put your formula into B3 exactly as is?
Was this answer helpful?

0 comments No comments
Anonymous

2017-10-22T05:18:20+00:00

oh wait haha i'm getting confused don't remove the $ from the 2, only from the B
Was this answer helpful?

0 comments No comments
Anonymous

2017-10-22T05:15:57+00:00

Thanks so much for this. Just a quick question if that's ok:

You said "if you paste the formula in cell B3, replace the number 1 with $B$2", so for the first line which is this:

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

Would it be changed to this:

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

I already did on the last formula I posted

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))

it will basically replace the $B$2 with the column number which is in $B$2

also before you paste the formula to the entire worksheet, remove the $ at the B then paste it across from A1 to AQ, then put the $ back on the letters in all the cells then remove the $ on the 2 before copying the formulas down
Was this answer helpful?

0 comments No comments