Remove Rows That Are NOT Duplicates

[Mongo the Magnificent]

I have a very large spreadsheet (45,000+ rows). I want to identify the
duplicate entries based on data that is in column O. I know how to do this
using conditional formatting and all works well. Thing is, I want to
ELIMINATE or HIDE the rows that are NOT duplicated. This is the exact
opposite of all the instructions I can find. They want to get rid of the
duplicates. I want to keep the dupes and and get rid of the single entries.

Can anyone help me?

 

[Pete_UK]

You can put a formula in a helper column similar to the one you use
for your conditional formatting, to return the words "unique" or
"duplicate", eg:

=IF(COUNTIF(O:O,O1)>1,"duplicate","unique")

and then copy this down. Then you can apply autofilter to this helper
column - if you just want to hide the uniques then select duplicate
from the filter pull-down, but if you want to delete them then select
unique from the filter pull-down, highlight all the visible rows, then
Edit | Delete Row. Select All from the filter pull-down and you are
left with only the duplicates. You can delete the helper column now.

Hope this helps.

Pete

 

[Lars-Åke Aspelin]

On Tue, 22 Jul 2008 12:50:00 -0700, Mongo the Magnificent <Mongo the

I have a very large spreadsheet (45,000+ rows). I want to identify the
duplicate entries based on data that is in column O. I know how to do this
using conditional formatting and all works well. Thing is, I want to
ELIMINATE or HIDE the rows that are NOT duplicated. This is the exact
opposite of all the instructions I can find. They want to get rid of the
duplicates. I want to keep the dupes and and get rid of the single entries.

Can anyone help me?

In a spare column, put the following formula

=COUNTIF(O$1:O$65536,O1)>1

drag it down as far as needed.

You can now filter out the duplicate rows, they are the ones with TRUE
in the spare column.

Hope this helps / Lars-Åke

 

[Gord Dibben]

In P1 enter =COUNTIF($O$1:$O$45000,O1)>1

Double-click to copy down.

Data>Filter>Autofilter for TRUE

Select column O and F5>Special>Visible cells only

Then Edit>Delete>Entire Row


Gord Dibben MS Excel MVP

On Tue, 22 Jul 2008 12:50:00 -0700, Mongo the Magnificent <Mongo the

 

[Mongo the Magnificent]

Hello Lars-Ã…ke... Your solution worked perfectly and was easy to implement.
Thank you so much for your assistance.
John