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

  • Thread starter Thread starter Aviva B
  • Start date Start date
A

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.
 
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
 
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!
 
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
 
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
Click to expand...

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
 
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
 
Back
Top