How can I display doubles

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

Guest

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top