Remove Rows That Are NOT Duplicates
 

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?

Remove Rows That Are NOT Duplicates
 

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

On Jul 22, 8:50*pm, Mongo the Magnificent <Mongo the
wrote:
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?

Remove Rows That Are NOT Duplicates
 

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

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

Remove Rows That Are NOT Duplicates
 

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

Double-click to copy down.

DataFilterAutofilter for TRUE

Select column O and F5SpecialVisible cells only

Then EditDeleteEntire Row


Gord Dibben MS Excel MVP

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

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?

Remove Rows That Are NOT Duplicates
 

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

"Lars-Ã…ke Aspelin" wrote:

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

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