Finding and Isolating Duplicates
 

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

Finding and Isolating Duplicates
 

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 datafilterautofilter.

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 editdeleteentire row, remove the
filter and the help column

Voila!


--


Regards,


Peo Sjoblom






"leimst" wrote in message
...
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

Finding and Isolating Duplicates
 

Hi Brian,

Put a header in A1, (you may need to insert a new row)
Then click on A1, go to DataFilterAdvanced 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" wrote in message
...
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

Finding and Isolating Duplicates
 

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

Brian

"MartinW" wrote in message
...
Hi Brian,

Put a header in A1, (you may need to insert a new row)
Then click on A1, go to DataFilterAdvanced 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" wrote in message
...
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

Finding and Isolating Duplicates
 

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

Brian

"Peo Sjoblom" wrote in message
...
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 datafilterautofilter.

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 editdeleteentire row,
remove the filter and the help column

Voila!


--


Regards,


Peo Sjoblom






"leimst" wrote in message
...
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