How do you remove UNIQUE items from a column and retain DUPLICATES?

**alemery76** · 02-21-2013, 10:51 AM

I need to remove only the unique data from a column and retain the duplicates. I cannot for the life of me find an answer online or figure it out.
I do not need to extract unique items, I do not need to remove duplicate items... I need to remove all unique (and the following colums) items and retain the duplicates.

5779423
5779431
5779432
5779440
5779453
5779459
5779462
5779462
5779503
5779523
5779524
5779545
5779573
5780158
5780162
5780162
5780221
5780263
5780285
5780293
5780294

**benishiryo** · 02-21-2013, 10:55 AM

hi alemery76, welcome to the forum. assuming data in A1, try this in B1:
=COUNTIF($A$1:$A$21,A1)=1

do an Autofilter by pressing CTRL + SHIFT + L. anything that is TRUE in column B are unique. you can then delete them

**alemery76** · 02-21-2013, 11:02 AM

I apologize, I wasn't specific.
I have 10 columns of data. Only Colum A contains unique and duplicate data. I want to delete, not seperate or extract, the unique data (and the following colums) for each row containing unique data in column A.

**ChemistB** · 02-21-2013, 11:34 AM

Benishiryo's solution will work, except it won't go in B, it'll go in Column 11. Then select all your data and filter on Col 11 as described by Beni. Select the visible rows and delete.

**MickG** · 02-21-2013, 11:35 AM

Try this:-

Please Login or Register  to view this content.

Regards Mick

**newdoverman** · 02-21-2013, 11:37 AM

Insert a helper column (new column B) and enter this formula in column B and copy down the length of the data:

=IF(COUNTIF(A:A,A1)>1,1,"")

Select all your data and sort on column B in descending order. All the duplicates will now be at the top of your data as identified by the 1 in column B. Delete all the data that doesn't have a 1 in column B.

Delete column B.