Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retain if atleast 4 entries
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retain if atleast 4 entries
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 at the first column of ID#s and if the ID# occurs less than 4 times, i delete those rows. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retain if atleast 4 entries
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 -----Original Message----- 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 at the first column of ID#s and if the ID# occurs less than 4 times, i delete those rows. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retain if atleast 4 entries
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 -----Original Message----- 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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retain if atleast 4 entries
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 -----Original Message----- 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." |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW I US "IF" FORMULS ATLEAST 15times, | New Users to Excel | |||
De-dup and retain new records only | Excel Worksheet Functions | |||
Combine Multiple Entries with differing amounts of entries | Excel Worksheet Functions | |||
retain formatting when concatenating | Excel Worksheet Functions | |||
retain cell formatting | Excel Discussion (Misc queries) |