Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Question - Delete set if any member meets criteria
I have a worksheet that contains student enrollment information in courses
and in some cases the same student has multiple items listed for the same course. The logic is as follows: IF the student ID AND the course ID are the same the are considered a SET. IF any member of the SET has a status = Completed, DELETE the SET. Here is an example of the "before" data: (comma seperated) Student ID,Course ID,Status 1,QUA100,Completed 1,QUA100,Enrolled 1,QUA100,No Show 1,QUA100,No Show 1,AO100,Enrolled 1,AO100,No Show 1,AO100,No Show 2,QUA100,Completed 3,QUA100,Enrolled 3,QUA100,No Show Here is an example of the "after" data: (comma seperated) Student ID,Course ID,Status 1,AO100,Enrolled 1,AO100,No Show 1,AO100,No Show 3,QUA100,Enrolled 3,QUA100,No Show Thanks in advance! Scott |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Question - Delete set if any member meets criteria
Try this code. It was simple.
Sub deletedups() StartRow = 2 RowCount = 2 Completed = False Do While Cells(RowCount, "A") < "" If Cells(RowCount, "C") = "Completed" Then Completed = True End If If (Cells(RowCount, "A").Value = _ Cells(RowCount + 1, "A").Value) And _ (Cells(RowCount, "B").Value = _ Cells(RowCount + 1, "B").Value) Then RowCount = RowCount + 1 Else If Completed = True Then Rows(StartRow & ":" & _ RowCount).Delete RowCount = StartRow Completed = False Else RowCount = RowCount + 1 StartRow = RowCount End If End If Loop End Sub "porter444" wrote: I have a worksheet that contains student enrollment information in courses and in some cases the same student has multiple items listed for the same course. The logic is as follows: IF the student ID AND the course ID are the same the are considered a SET. IF any member of the SET has a status = Completed, DELETE the SET. Here is an example of the "before" data: (comma seperated) Student ID,Course ID,Status 1,QUA100,Completed 1,QUA100,Enrolled 1,QUA100,No Show 1,QUA100,No Show 1,AO100,Enrolled 1,AO100,No Show 1,AO100,No Show 2,QUA100,Completed 3,QUA100,Enrolled 3,QUA100,No Show Here is an example of the "after" data: (comma seperated) Student ID,Course ID,Status 1,AO100,Enrolled 1,AO100,No Show 1,AO100,No Show 3,QUA100,Enrolled 3,QUA100,No Show Thanks in advance! Scott |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Question - Delete set if any member meets criteria
Works fine, as long as the data is sorted by Student ID, and Course ID.
Probably should include code to sort the list first, just to make sure. -- Regards, Bill Renaud |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Question - Delete set if any member meets criteria
Thanks!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if meets criteria add 1 | Excel Worksheet Functions | |||
Add up a value that meets 2 criteria | Excel Discussion (Misc queries) | |||
Do Nothing If Meets Criteria | Excel Discussion (Misc queries) | |||
How do I go to the next cell that meets criteria | Excel Discussion (Misc queries) | |||
Max value that meets a criteria | Excel Worksheet Functions |