Remove unique rows and keep duplicates

**tmduffy** · 10-27-2010, 08:56 AM

Hello,

Currently, my spreadsheet is advanced filtered to remove duplicates considering both columns. My goal is to have a list of Names (Column B) that have a duplicate in Column B, but a different ID in Column A. All of the names in Column B that do no have a duplicate in Column B, need to be removed. I tried running a macro from this forum, but received an error message.

Attached is a sample of current and goal data (remove non-duplicated names in red font):

Current
ID Name
26594 17-amino acid co
39757 17-amino acid co
20084 18-amino acid
311 2-propanol
3725 2-pen
8300 2-pen
23380 20/20 artficl tear
2005 222
2236 222

Goal
ID Name
26594 17-amino acid co
39757 17-amino acid co
3725 2-pen
8300 2-pen
2005 222
2236 222

Any help is appreciated.

**alansidman** · 10-29-2010, 08:25 AM

This solution is for Excel versions earlier than 2007.
First, I would do a conditional format on all duplicates. Use the formula found in this link on Column B.

http://office.microsoft.com/en-au/ex...001136616.aspx

then create a UDF as shown here

http://www.datapigtechnologies.com/f...rtonColor.html

Once you have sorted the file by color, you can then delete the appropriate rows very quickly.

For 2007 users, use conditional format wizard, sort and delete.

Alan

**tmduffy** · 10-29-2010, 10:34 AM

Thank you Alan,

I will give it a try.

Terry

**gmahlert** · 10-29-2010, 03:13 PM

Hello Terry,
I wrote a function to move duplicates to another sheet.
Take a look, maybe it will suit your needs.

Gordon in Rovereto Italy

Please Login or Register  to view this content.

**martindwilson** · 10-30-2010, 08:31 AM

simplest is to count them .
in a spare column on the same row assuming data starts in b2
=countif(B:B,B2)>1
auto fill down to end of data in col b
now filter that column by false
you can now delete those rows in on go
or else filter on true and copy visible rows to somewhere else to work on

on re -reading this i see you are already using advanced filter since you know how to do it why not reverse the criteria so as to filter on those you dont want, then simply delete the entire lot of visible rows