How can I display doubles

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Henry88th - 06 Nov 2007 15:56 GMT

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.

Reply to this Message

Peo Sjoblom - 06 Nov 2007 16:04 GMT

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

Signature

Regards,

Peo Sjoblom

> Hello all,
>
[quoted text clipped - 4 lines]
>
> Your input is greatly appreciated.

Reply to this Message

Don Guillett - 06 Nov 2007 16:13 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hello all,
>
[quoted text clipped - 4 lines]
>
> Your input is greatly appreciated.

Reply to this Message

Sandy Mann - 06 Nov 2007 16:15 GMT

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

Signature

HTH

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Hello all,
>
[quoted text clipped - 4 lines]
>
> Your input is greatly appreciated.

Reply to this Message

Rate this thread: