How to remove duplicates?

[staceyjkelly10]

Hello, how do I remove a duplicate record and also that very record - e.g there are 5 email addresses on my spreadsheet for [email protected] how do I get rid of ALL of these, therefore not leaving any trace of this email at all. Is there an easy formula? If it is a v-lookup please explain how I do this, I know only basic excel.

Thank you!

[JBeaucaire]

In an adjacent column you can use a COUNTIF() formula to count how many times that email address string is listed in that column. Then delete all the rows with a count of > 1.

Assume emails are in column A. Put a generic TITLE in A1, all the emails from A2 down.

Then the formula in B2 would be:

=COUNTIF(A:A, A2)

...and copy down.

Now turn on the Data > Filter for those two columns, filter column B for values > 1.... Delete all the visible rows. Turn off the FILTER and you're left with only the emails that were unique in the list.

[TMS]

The easiest way, I think, would be to add a Helper column. In the Helper column, use COUNTIF to count the number of occurrences of each email address. Then filter on the Helper column for counts greater than one.

Delete all the visible rows and switch off filtering.

Of course, you can be selective about which groups of records to delete.


Regards, TMS

[raystafarian]

Another way would be to use conditional formatting - highlight rules - highlight duplicates
then you could filter by color and just delete them

[dilipandey]

Hi Stacey,

Welcome to the forum.

Since you are using Excel 2010, go to Data -> Remove Duplicates. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>