Retain if atleast 4 entries

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

Hi
I have a unique problem here. I have got an excel file
with almost 2500 rows of data in it. Coulumn "A" contains
the ID # of companies. Each company might have more than
one data entry (row), so it looks something like this:

A B C D E
34982 data data data data
34982 data...
5533812 data...
5533812 data...
5533812 data...
5533812 data...
5533812 data...
456832 data...
456832 data...
456832 data...

What i need to do is to write a program which will look at
the first column of ID#s and if the ID# occurs less than 4
times, i delete those rows. Basically if a company has
ATLEAST 4 data entries, i have to keep it, if not, delete
entire rows of that company.
Any kind of suggestion would be very helpful! Thanks a lot
in advance.
 
Randy,

assuming your data is sorted in numerical order by company
and the starts in cell A2, this very crude bit of code
will do the job:

Sub DeleteCompanies()
LastRow = ActiveSheet.UsedRange.Count
For x = 2 To LastRow
HowMany = Application.WorksheetFunction.CountIf(Range_
(Cells(x, 1), Cells(LastRow, 1)), Cells(x, 1).Value) - 1

If HowMany < 3 Then
For z = (x + HowMany) To x Step -1
Rows(z).Delete
Next z
LastRow = LastRow - HowMany
x = x - 1
Else
x = x + HowMany
End If

Next x

End Sub

I would save a copy before you start deleting though!

Pete
I have a unique problem here. I have got an excel file
with almost 2500 rows of data in it. Coulumn "A" contains
the ID # of companies. Each company might have more than
one data entry (row),
What i need to do is to write a program which will look
Click to expand...
at the first column of ID#s and if the ID# occurs less
than 4 times, i delete those rows.
 
Thanks Pete for your help. Your assumptions are correct.
But when i try to run the code, the compiler
selects "Range" in
-:
HowMany = Application.WorksheetFunction.CountIf(Range_
(Cells(x, 1), Cells(LastRow, 1)), Cells(x, 1).Value) - 1
:-
AND gives an error "Sub or Function not defined."

Does it need to be defined at the beginning of the
program? Please let me know. Thanks a lot once again!
Randy
 
Randy
The following works
Sub testDel()
Dim myRng As Range
Dim c, s, v
Dim count As Integer, nr As Integer, z As Integer
With Worksheets(1)
Set myRng = .Range("A2", .Cells(.Rows.count, "A").End
(xlUp))
End With
nr = myRng.Rows.count
For s = 2 To nr + 1
Cells(s, 1).Select
v = Cells(s, 1).Value
z = Application.CountIf(myRng, v)
If z < 4 Then
Selection.EntireRow.Delete
count = count + 1
End If
Next s
MsgBox count & " Records have been deleted", , "Deleted _
Record Count"
End Sub

Any problem contact me
Regards
Peter
 
Randy,

it worked fine when I tried it on a sample. The only thing
I can suggest is deleting the underscore character and
have that whole bit segment on one line. It only ended up
looking like that because of the formatting on the NG.

In any event, it looks like Peter's code will have sorted
you out anyway.

Pete
 
Back
Top