Duplicate sorts

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 40K rows of data....some data is unique while some is
duplicated,..occuring at least twice...I want to get only the duplicated
data....how do i filter it? Unique records give ALL of the unique data. I
need just the data that is being repeated. Pls help. Thanks
 
Say your duplicated KEYS are in column A. Enter formula =COUNTIF($A:$A,A2)>1
in a helper column, fill it down until your last row, and Autofilter helper
column to TRUE!

Regards,
Stefi

„Malik†ezt írta:
 
Add a helper column, and a header, with a formula of

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

copied down, and then set criteria of the header and TRUE, and filter using
those criteria.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
After showing unique data with Advanced Filter, you can invert the selection
to show just duplicated data. Select the data range then press (or select):

1. Alt+; (Edit > Goto > Special > Visible Cells)
2. Alt+DFS (Data > Filter > Show All)
3. Ctrl+9 (Format > Rows > Hide)

You can then select data then Alt+; again and copy to a new sheet.
To show all data again select all cells and unhide rows.
(This method can be used to invert any filtered selection.)
 
thanks everyone fr help.....

Lori said:
After showing unique data with Advanced Filter, you can invert the selection
to show just duplicated data. Select the data range then press (or select):

1. Alt+; (Edit > Goto > Special > Visible Cells)
2. Alt+DFS (Data > Filter > Show All)
3. Ctrl+9 (Format > Rows > Hide)

You can then select data then Alt+; again and copy to a new sheet.
To show all data again select all cells and unhide rows.
(This method can be used to invert any filtered selection.)
Click to expand...
 
Back
Top