Finding and Isolating Duplicates

[leimst]

Hi,

I have a single column of numeric data that I want to search for duplicates.
I know that I can use a "countif" statement with Conditional Formatting to
highlight all the duplicates (e.g. ="COUNTIF(A$1:A$100, A1)>1") but then is
there a way to remove all of the remaining unique numbers or maybe using
some sort of megaformula copy the highlighted cells along with any other
data in that particular row to another spreadsheet?

Thanks,

Brian

 

[Peo Sjoblom]

Use a help column using you example starting in B2 after you insert a top
line and put a header in A1, then use this formula

=COUNTIF($A$2:A2,A2)<=1

copy down to B101 or to the last adjacent cell in A (you can move the mouse
to the lower right corner of the formula cell and when it changes into a
thin from a thick cross double click to copy down)

select both columns

do data>filter>autofilter.

Depending on if you want to remove the duplicates or remove unique items,
assume you want to remove all duplicates, filter on B, select FALSE and then
select the visible area in A and B and do edit>delete>entire row, remove the
filter and the help column

Voila!


--


Regards,


Peo Sjoblom

 

[MartinW]

Hi Brian,

Put a header in A1, (you may need to insert a new row)
Then click on A1, go to Data>Filter>Advanced Filter
Check 'Copy to another location'
Put a destination cell in the copy to box (say $G$9 or whatever)
Check 'Unique values only'
OK and you are done.

HTH
Martin

 

[leimst]

Thanks for the help. That's something I didn't even know was available!

Brian

 

[leimst]

This is a great formula that I've never seen used before. I've always
predefined the entire range. Thanks for the help!

Brian