VBA Question - Delete set if any member meets criteria

[Guest]

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

 

[Guest]

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

 

[Bill Renaud]

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.

 

[Guest]

Thanks!