Remove Duplicates (but keep the one with an email contact!)?

[NURDS]

Hi everyone. I'm a new member based in the rainy north of England! I would consider myself as "outstandingly average" at Excel and have been using this site for years. For the first time, I have a problem that I can't fix without your specific help.

I have a list of our customers. I need to remove the duplicate customers (which, ordinarily is easily done using the duplicates function). However, I need remove all duplicates but keep the customer line that contains the email??

I have uploaded a sample file which hopefully illustrates the problem. As you will see, some customers have one email, some have more than one email and some have none. I just need to figure out how to keep one single line of each customer (if no email exists) and where an email exists, keep one line of data (containing an email contact)??

Hopefully this makes sense.

 

[p45cal]

No file

 

[NURDS]

No file

Thanks p45cal - should be attached now.

 

[p45cal]

First do a straight Remove Duplicates on all 3 columns.
Then temporarily add this formula to cell D2:
=AND(COUNTIFS($A$2:$A$9,A2,$B$2:$B$9,B2)>1,ISBLANK(C2))
making sure that the ranges $A$2:$A$9 and $B$2:$B$9 cover your data.
All those with TRUE in column D can be deleted, so Autofilter the 4 columns, show only True in Column D, select the visible cells and either:
right-click and choose delete row and confirm you're happy to delete the entire rows of the sheet, or, if you think that's a bit dangerous,
press the delete key on the keyboard (which will leave blank rows on the sheet but you can always sort that range to put all the blank rows at the bottom).
Either way, remove the autofilter and delete the formulae in column D.

if a customer has more than one email address, all emails will remain.

 

[NURDS]

Fantastic! Thanks for the solution p45cal

 

[jeffreyweir]

Alternatively, a PivotTable is perfect for this kind of thing, if you want to eyeball the duplicate email addresses and then work out which to discard.