De-duping a mailing list

Anonymous
2017-10-21T04:39:27+00:00

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.

Microsoft 365 and Office | Excel | For home | Windows

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

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2017-10-22T06:02:35+00:00

    let's test one using this formula:

    paste this in B3:

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

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

    paste this in C3:

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

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

    Was this answer helpful?

    0 comments
  2. Anonymous
    2017-10-22T06:10:40+00:00

    Ok I tried that one and I still got the error. The following are the screenshots of the formulas in the cells - B3 and C3:

    Was this answer helpful?

    0 comments
  3. Anonymous
    2017-10-22T06:12:36+00:00

    Sorry the screenshot below shows what it looks like in B3 but when I click enter, it gives me the error again.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2017-10-22T06:17:17+00:00

    ok paste this in B3:

    =IF(ISNA(VLOOKUP($A3,[test.xlsx]Home!$A$1:$AQ$61,4,FALSE))

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

    Was this answer helpful?

    0 comments
  5. Anonymous
    2017-10-22T06:20:16+00:00

    I still get the error - see screenshot below:

    Was this answer helpful?

    0 comments