How to delete all duplicate records - without keeping one of them

[Aviva B]

If I have a list of records, I know I can get rid of duplicate records using
an advanced filter - unique records only. But what if I want any record that
is duplicated to be deleted entirely from the list?
For example, if I have a list of:
1
2
2
3

How can I get only 1 and 3 to be left?

Thanks.

 

[Mike H]

Hi,

Note this will delete BOTH duplicates.
In a helper column enter this formula and drag down to the length of your
data column

=COUNTIF($A$1:$A$12,A1)

Select both columns and sort on the helper column. Select all rows greater
than 1 and delete

Mike

 

[Ms-Exl-Learner]

Assume that you are having the below Values in A Column

Values
1
2
2
3

In B2 Cell Paste this formula
=IF(COUNTIF(A:A,A2)=1,"NO DUPLICATES",IF(COUNTIF(A:A,A2)>1,"DUPLICATES",""))

Copy the B2 cell and paste it to the remaining cells of B Column Based on
the A Column Values.

Now apply the Filter for the B Column and select Duplicates, now it will
show the Duplicate details. Now place the cursor in the first cell of
Duplicates that is next to the header column cell and press Shift+Spacebar it
will select the current row and press Cntrl+Shift+Down Arrow which will
select the total range of Duplicate values and do right click and press D.
Remove the Autofilter but pressing Alt+D+F+F.

Now you can see your desired results.

Remember to Click Yes, if this post helps!

 

[Otávio Alves Ribeiro]

Hi there.
Well, you did not tell us with which version of Excel you are working but,
if you are using Excel 2007, you can select your data range and choose Remove
Duplicates on Data tab.
Regards,
Otávio

 

[Ron Rosenfeld]

Hi there.
Well, you did not tell us with which version of Excel you are working but,
if you are using Excel 2007, you can select your data range and choose Remove
Duplicates on Data tab.
Regards,
Otávio

How did you get that to remove all the records that had been duplicated, per
the OP's request?

When I tried that, it left one of the duplicates behind.
--ron

 

[Ashish Mathur]

Hi,

Let's say that data is in range B9:B13. Type the heading in B8, say
Numbers. In cell E9, type =COUNTIF($B$9:$B$12,B9)=1. Now go to Data >
Filter > Advanced Filter and select "Copy to another location". In the list
range, select B8:B13 and in the criteria range, select E8:E9. In the copy
to box, select any blank cell. Click on OK. You will get the answer as 1
and 3.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com