How to delete rows

[Urlant]

Hello there,


I have a document that contains information about students, such as the student ID, the discipline code and the grades corresponding to a specific discipline. I will attach the document so you guys can better understand it.

The problem is that I need to guarantee that every student has taken all the disciplines. So, what I have to do is to analyse the student ID and check if all discipline's codes corresponds to that student. There is a total of 49 disciplines represented as CD01, CD02, CD03, ..., CD49 (Discipline Code). So, all students must have taken all the 49 courses. If a student has not completed all the courses, say for example, he has completed 48 (or less), I have to delete all the rows that contains informations about that students (we can do that by checking his ID I guess).

The picture below shows a case of a student who has a discipline missing, in this case, the discipline with code equals to CD02. So, this student with ID: 200727040017, must have all the rows (his information) deleted. In the end of the process, only the information of students who have completed all disciplines must remain in the document.

I would really appreciate if you guys could help me in this one. I will also put the document here in case you guys want to use it to work with. If you guys can post the resulting document after solving the problem I'd also appreciate.

Thanks a lot.

[mehmetcik]

I would use a macro for this.

first sort your data by column A and then column B

Then put this formula into E2 and Fill Down

=IF(A2=A1,"",1)

Then put this formula into F2 and Fill Down

=IF(E2=1,1,IF(B2=B1,F1,F1+1))

Then select Select Column F and find the first 1. if Column E in the previous Row does not contain 49 then delete the previous selection.

Sorry I got pulled away. This code seems to work. Please test carefully, as I have lost focus.

Sub Macro1()

    LR = Cells(Rows.Count, 1).End(xlUp).Row
    Start = 2
    
    Range("E3:E" & LR).FormulaR1C1 = "=IF(RC[-4]=R[-1]C[-4],"""",1)"
    Range("F3:F" & LR).FormulaR1C1 = "=IF(RC[-1]=1,1,IF(RC[-4]=R[-1]C[-4],R[-1]C,R[-1]C+1))"
    
    Range("E2:F" & LR).Value = Range("E2:F" & LR).Value
    
    Cells(LR + 1, 5).Value = 1

Loop1:

    
    Z = Columns(5).Find(1, LookIn:=xlValues, Lookat:=xlWhole).Row
    
    If Cells(Z - 1, 6).Value <> 49 Then
        Rows(Start & ":" & Z - 1).EntireRow.Delete
        LR = Cells(Rows.Count, 1).End(xlUp).Row
    End If
    
    Cells(Z, 5).Clear
    Start = Z
    
    If Start < LR Then GoTo Loop1
    
End Sub

[Urlant]

Hey, thanks for answering. I sorted the two first columns (A and B) using the option A-Z. Then I copied the formula: =IF(A2=A1,"",1) in E2 and filled down, but I got an error saying: 'We found a problem in this formula. Are you not trying to type a formula...?' (I translated the error from the portuguese version, so it might be slightly different). Do you know what is causing this error? Sorry, I have just a little experience with excel.


obs: The excel version I'm using is excel 2016. Thanks in advance.

[peterrc]

Hi Urlant,

I have created a Pivot Table, see attached, which highlights the students who have not achieved the 49 Diciplines.
In addition it also shows the students who achieved 50?
This is as a result of duplications.
Hope this helps.

Regards

peterrc