How can I display doubles

H

Henry88th

Hello all,

I have a worksheet with over 15,000 lines. I would like to be able to retain
only those lines which occur two or more times and simply get rid of the
rest. Any ideas?

Your input is greatly appreciated.
 
P

Peo Sjoblom

You can use a filter, in a help column put this formula assuming that your
data is A2:A15001 with a header in A1 insert a new column B unless it is
already free and in B2 put

=COUNTIF($A$2:$A$15001,A2)


copy down, you can double click the lower right corner of B2 once you put
the formula there
and it will copy down automatically


then apply autofilter and filter custom greater or equal to 2 in the help
column,
then just copy and paste the filtered result to another sheet


--


Regards,


Peo Sjoblom
 
D

Don Guillett

One way?

Sub keepif2()
For i = 15000 to 1 step -1
If Application.CountIf(Columns(1), Cells(i, "a")) _
< 2 Then Rows(i).Delete
Next
End Sub
 
S

Sandy Mann

Us a helper column and the formula:

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

and copy down all 1500 rows which will return TRUE or FALSE . Then
Autofilter on False and delete the visible rows

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
Top