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

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

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

VBA Question - Delete set if any member meets criteria
 

Thanks!