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-21T05:15:40+00:00

    it would help to find a solution if we can see the geography of you spreadsheet

    Was this answer helpful?

    0 comments
  2. Anonymous
    2017-10-21T05:31:03+00:00

    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

    Was this answer helpful?

    0 comments
  3. Anonymous
    2017-10-21T06:08:58+00:00

    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.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2017-10-21T06:27:41+00:00

    Ok thanks - I'll give it a try.

    Much appreciated.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2017-10-22T02:28:48+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.

    Was this answer helpful?

    0 comments