Delete rows listed less than 8 times???

A

anilos81

OK, so here is my problem:

I am at work and have to take spreadsheets with about 25,000 rows o
data and delete any records that appear less than 8 times.

To be more specific, I have three columns:

name
office
phone number

When I sort it by name John Doe might appear 10 times while Jane Do
only appears twice. Because Jane Doe is coming up LESS THAN 8 time
all of her rows need to be deleted.

Can someone please help because this is an ongoing project and I can'
see my company wasting the man hours to do it manually!

Let me know if I haven't explained well enough and I'll try to clarify


Amand
 
A

ashish13

sort you table by name.

go to Data-> subtotals

At each change in name use function count on field Phone number

this will give you a count for every name
OR

create a pivot table and the use the vlookup function

I am in a rush so my apologies if this is too cryptic
 
J

John Michl

Add another column to count the number of occurences a particular name
shows up. Assuming the name field is in column A, use this formula in
row 2 of the new column then copy down the length of the sheet.
=IF(SUMPRODUCT(--($A$1:$A$6=A1))<3,"Delete","Keep")

Sort the table by the new column and all of the Delete's will be
bunched together.

- John Michl
www.JohnMichl.com
 
J

John Michl

Add another column to count the number of occurences a particular name
shows up. Assuming the name field is in column A, use this formula in
row 2 of the new column then copy down the length of the sheet.
=IF(SUMPRODUCT(--($A$1:$A$6=A1))<3,"Delete","Keep")

Sort the table by the new column and all of the Delete's will be
bunched together.

- John Michl
www.JohnMichl.com
 
R

Ron Coderre

Assumption:
Names are listed in Column A
•D1: NameCount
•D2: =COUNTIF($A:$A,A2)
•Copy that formula down
•Select your range (including the counts)
•Date>Filter>Autofilter
•Click on the NameCount dropdown
-Select Custom
-Is less than or equal to 8
-Click [OK]

•Now just select from A2 through the bottom of the list
•Edit>Delete (you can only delete entire rows in autofilter)

Data>Filter>Autofilter to remove autofilter

Does that help?

Regards,
Ron
 
B

Bob Phillips

Hi Amanda,

This should do it

Sub Redundancy()
Dim iLastrow As Long
Dim i As Long
Dim rng As Range
Dim iStart As Long
Dim iEnd As Long
Dim sTemp

iLastrow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(1).Resize(iLastrow).Sort key1:=Range("A1"), header:=xlGuess
iStart = 1: sTemp = Range("A1").Value
For i = 2 To iLastrow
If Cells(i, "A").Value <> sTemp Then
iEnd = i - 1
If i - iStart < 8 Then
If rng Is Nothing Then
Set rng = Rows(iStart & ":" & iEnd)
Else
Set rng = Union(rng, Rows(iStart & ":" & iEnd))
End If
End If
iStart = i
sTemp = Cells(i, "A").Value
End If
Next i

If Not rng Is Nothing Then
rng.Delete
End If

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
A

anilos81

John said:
Add another column to count the number of occurences a particular name
shows up. Assuming the name field is in column A, use this formula in
row 2 of the new column then copy down the length of the sheet.
=IF(SUMPRODUCT(--($A$1:$A$6=A1))<3,"Delete","Keep")

Sort the table by the new column and all of the Delete's will be
bunched together.

- John Michl
www.JohnMichl.com
Click to expand...

Thanks John,

I tried it but the results were not correct.
 
J

John Michl

Did you change the <3 in my formula to <8? I tested it with a small
database and it worked but I used less that 3 as my criteria.

- John
 
A

anilos81

Bob said:
Hi Amanda,

This should do it

Sub Redundancy()
Dim iLastrow As Long
Dim i As Long
Dim rng As Range
Dim iStart As Long
Dim iEnd As Long
Dim sTemp

iLastrow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(1).Resize(iLastrow).Sort key1:=Range("A1"), header:=xlGuess
iStart = 1: sTemp = Range("A1").Value
For i = 2 To iLastrow
If Cells(i, "A").Value <> sTemp Then
iEnd = i - 1
If i - iStart < 8 Then
If rng Is Nothing Then
Set rng = Rows(iStart & ":" & iEnd)
Else
Set rng = Union(rng, Rows(iStart & ":" & iEnd))
End If
End If
iStart = i
sTemp = Cells(i, "A").Value
End If
Next i

If Not rng Is Nothing Then
rng.Delete
End If

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"anilos81" <[email protected]
wrote in
message news:[email protected]...
OK, so here is my problem:

I am at work and have to take spreadsheets with about 25,000 rows of
data and delete any records that appear less than 8 times.

To be more specific, I have three columns:

name
office
phone number

When I sort it by name John Doe might appear 10 times while Jane Doe
only appears twice. Because Jane Doe is coming up LESS THAN 8 times
all of her rows need to be deleted.

Can someone please help because this is an ongoing project and can't
see my company wasting the man hours to do it manually!

Let me know if I haven't explained well enough and I'll try t clarify.
Click to expand...


Thank you so much Bob! You are a lifesaver! For some reason it leave
one random record on the very bottom but that's no biggie, I can alway
delete it. Thanks!

Amand
Click to expand...
 
A

anilos81

John said:
Did you change the <3 in my formula to <8? I tested it with a small
database and it worked but I used less that 3 as my criteria.

- John
Click to expand...

Hi John,

Yes I did, it works for the first eight records but then it goes all
crazy! I think it has to do with the fact that the names are listed a
varying number of times... In any case Bob's macro worked very well,
and I appreciate your help!

Amanda
 
Top