Delete rows listed less than 8 times???
 

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 I 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 to clarify.


Amanda


--
anilos81
------------------------------------------------------------------------
anilos81's Profile: http://www.excelforum.com/member.php...o&userid=28687
View this thread: http://www.excelforum.com/showthread...hreadid=483688

Delete rows listed less than 8 times???
 

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

Delete rows listed less than 8 times???
 

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

Delete rows listed less than 8 times???
 

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

Delete rows listed less than 8 times???
 

Small mistake...Change the 3 to 8.

Delete rows listed less than 8 times???
 

Assumption:
Names are listed in Column A
•D1: NameCount
•D2: =COUNTIF($A:$A,A2)
•Copy that formula down
•Select your range (including the counts)
•DateFilterAutofilter
•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
•EditDelete (you can only delete entire rows in autofilter)

DataFilterAutofilter to remove autofilter

Does that help?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=483688

Delete rows listed less than 8 times???
 

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" wrote in
message ...

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 I 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 to clarify.


Amanda


--
anilos81
------------------------------------------------------------------------
anilos81's Profile:
http://www.excelforum.com/member.php...o&userid=28687
View this thread: http://www.excelforum.com/showthread...hreadid=483688

Delete rows listed less than 8 times???
 

John Michl Wrote:
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

Thanks John,

I tried it but the results were not correct.


--
anilos81
------------------------------------------------------------------------
anilos81's Profile: http://www.excelforum.com/member.php...o&userid=28687
View this thread: http://www.excelforum.com/showthread...hreadid=483688

Delete rows listed less than 8 times???
 

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

Delete rows listed less than 8 times???
 

Bob Phillips Wrote:
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"
wrote in
message ...

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 I
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 to
clarify.


Amanda


--
anilos81

------------------------------------------------------------------------
anilos81's Profile:
http://www.excelforum.com/member.php...o&userid=28687
View this thread:
http://www.excelforum.com/showthread...hreadid=483688



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

Amanda


--
anilos81
------------------------------------------------------------------------
anilos81's Profile: http://www.excelforum.com/member.php...o&userid=28687
View this thread: http://www.excelforum.com/showthread...hreadid=483688

Delete rows listed less than 8 times???
 

John Michl Wrote:
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

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


--
anilos81
------------------------------------------------------------------------
anilos81's Profile: http://www.excelforum.com/member.php...o&userid=28687
View this thread: http://www.excelforum.com/showthread...hreadid=483688