Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Rows based on criteria in excel | Excel Discussion (Misc queries) | |||
Want to delete rows | Excel Discussion (Misc queries) | |||
Q: delete rows | Excel Discussion (Misc queries) | |||
delete rows from multiple worksheets | Excel Worksheet Functions | |||
How can I delete rows from Pivot Tables in Excel 2000 as in 97 | Excel Discussion (Misc queries) |