need help isolating duplicates

[Guest]

Hi,

I have seen plenty of solutions for deleting duplicate cells, but I would
like to know how to copy duplicates to a new worksheet so that I can isolate
them and determine how they infiltrated my data in the first place.

I have 5 columns of data (A-E) with the first row of each column as a
header. The data consists of rows 2 - 3672. Apparently some of the entries
have a duplicate value in column E, I would like to find all rows which
contain column E data that is duplicated (or triplicated, quadruplicated,
etc., etc.) in another row and copy them all to a new file or new worksheet.

I appreciate your help.

 

[Dave Peterson]

I'd add a new column (F?) and put a formula in it:

headers in F1 and this in F2:

If you want to include the first row as a duplicate:
=countif(e:e,e2)
and drag down.

If you want to ignore the first appearance of a value:
=countif($e$2:$e2,e2)
And drag down.

Then filter by this column and show values greater than 1. Copy and paste the
visible cells to a new worksheet (if you really need to--maybe using the filter
would be sufficient.)

And you may want to take a look at Chip Pearson's site.
He has a bunch of techniques for working with duplicates at:
http://www.cpearson.com/excel/duplicat.htm

 

[Guest]

that did it, thanks!

I'd add a new column (F?) and put a formula in it:

headers in F1 and this in F2:

If you want to include the first row as a duplicate:
=countif(e:e,e2)
and drag down.

If you want to ignore the first appearance of a value:
=countif($e$2:$e2,e2)
And drag down.

Then filter by this column and show values greater than 1. Copy and paste the
visible cells to a new worksheet (if you really need to--maybe using the filter
would be sufficient.)

And you may want to take a look at Chip Pearson's site.
He has a bunch of techniques for working with duplicates at:
http://www.cpearson.com/excel/duplicat.htm