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:39:18+00:00

can you share the file in onedrive? I'll take a look at it and fix it
Was this answer helpful?

0 comments No comments
Anonymous

2017-10-22T05:41:30+00:00

The following is a screenshot of the error - it seems that the part in red might be contributing. Are you able to determine anything from the screenshot?
Was this answer helpful?

0 comments No comments
Anonymous

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

The following is a screenshot of the error - it seems that the part in red might be contributing. Are you able to determine anything from the screenshot?

did you setup your names sheet this way?

where there are numbers below the heading names that replaces that reference with the actual column numbers? or you can manually type the column numbers to 44 cells in all instances to each formula
Was this answer helpful?

0 comments No comments
Anonymous

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

The following is a screenshot of the error - it seems that the part in red might be contributing. Are you able to determine anything from the screenshot?

did you setup your names sheet this way?

where there are numbers below the heading names that replaces that reference with the actual column numbers? or you can manually type the column numbers to 44 cells in all instances to each formula

what is going to happen if you manually replaced all those red things with the actual column numbers would be if you pasted that formula in b3, you would put the number 2 to replace the red ones on that cell in all 44 cells before you can copy the formula down to all the cells adjacent to the names then when you paste it in C3 you would replace the red things with the number 3 and so on till you've done all 44 cells across the spreadsheet
Was this answer helpful?

0 comments No comments
Anonymous

2017-10-22T05:56:59+00:00

Yes I set the name sheet that way but the name of the sheet is called "names only" - not "names". I'm sorry but I don't understand your explanation. All I can say is that I set it up as you suggested and I pasted in the formula at B3 and got the error. I'm not able to share the file at this time. Maybe I need to come back to this later on and see if I can re-read and understand it.
Was this answer helpful?

0 comments No comments