EXCEL, How to populate lookup name + number of courses completed over a separated chart?

[DarkJester89]

Trying to figure out how to populate a list of:

1. of people completed all courses (column H)
2. their names (Column I)
3. and name of who haven't completed all 11 (Column J)

Smith, John only has 10 courses completed.

My problem is I don't know how to get lookups to look at split columns of data, I dont need the date completed or the method.

• Excel Version (2016)
• Excel Environment (desktop)
• Excel Language (English)
• Your Knowledge Level (Beginner)
• Include all data that may be impacting your issue, including samples and mock-ups to help illustrate things more clearly. (Workbook below)
• Explain what you have tried, and show the formula(s) you are using and/or have tried, and include any error(s) you are getting. I don't know where to start with a chart like this
• Just need Formula solution
• Repetitive task

Work book

Thank you in advance.

 

[DarkJester89]

I'm still trying to find an answer and can't figure out the best route to start with something like this.

 

[mumps]

Try:

Sub PopulateData()
    Application.ScreenUpdating = False
    Dim v As Variant, dic As Object, i As Long, RowCount As Long, cnt As Long
    v = Range("B2", Range("B" & Rows.Count).End(xlUp)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        If Not dic.exists(v(i, 1)) Then
            dic.Add v(i, 1), Nothing
            Range("B1").AutoFilter 1, v(i, 1)
            RowCount = [subtotal(103,B:B)] - 1
            Range("B1").AutoFilter
            If RowCount = 11 Then
                cnt = cnt + 1
                Cells(Rows.Count, "I").End(xlUp).Offset(1) = v(i, 1)
            ElseIf RowCount < 11 Then
                Cells(Rows.Count, "J").End(xlUp).Offset(1) = v(i, 1)
            End If
        End If
    Next i
    Cells(Rows.Count, "H").End(xlUp).Offset(1) = cnt
    Application.ScreenUpdating = True
End Sub

 

[DarkJester89]

Try:

Sub PopulateData()
    Application.ScreenUpdating = False
    Dim v As Variant, dic As Object, i As Long, RowCount As Long, cnt As Long
    v = Range("B2", Range("B" & Rows.Count).End(xlUp)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        If Not dic.exists(v(i, 1)) Then
            dic.Add v(i, 1), Nothing
            Range("B1").AutoFilter 1, v(i, 1)
            RowCount = [subtotal(103,B:B)] - 1
            Range("B1").AutoFilter
            If RowCount = 11 Then
                cnt = cnt + 1
                Cells(Rows.Count, "I").End(xlUp).Offset(1) = v(i, 1)
            ElseIf RowCount < 11 Then
                Cells(Rows.Count, "J").End(xlUp).Offset(1) = v(i, 1)
            End If
        End If
    Next i
    Cells(Rows.Count, "H").End(xlUp).Offset(1) = cnt
    Application.ScreenUpdating = True
End Sub

Thank you, this works but is it possible for anything in formula format?

 

[mumps]

You are very welcome. I'm not sure if there is a formula solution.

 

[DarkJester89]

I was able to reformat the table, if anyone is able to assist.

How can I extract list of students by counting the number of classes completed.

Class 1-A is to be counted the same as Class 1.

Thank you in advance.

Workbook

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: reddit
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[mumps]

Try:

Sub PopulateData()
    Application.ScreenUpdating = False
    Dim v As Variant, dic As Object, i As Long, RowCount As Long
    v = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        If Not dic.exists(v(i, 1)) Then
            dic.Add v(i, 1), Nothing
            Range("A1").AutoFilter 1, v(i, 1)
            RowCount = [subtotal(103,A:A)] - 1
            Range("A1").AutoFilter
            If RowCount = 11 Then
                Cells(Rows.Count, "F").End(xlUp).Offset(1) = v(i, 1)
            ElseIf RowCount < 11 Then
                Cells(Rows.Count, "G").End(xlUp).Offset(1) = v(i, 1)
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[DarkJester89]

For equal to 11
=SORT(UNIQUE(FILTER($A$2:$A$33,COUNTIF($A$2:$A$33,$A$2:$A$33)=11)))

For less than 11
=SORT(UNIQUE(FILTER($A$2:$A$33,COUNTIF($A$2:$A$33,$A$2:$A$33)<11)))

Thank to Mh_Mike from another social media platform.

 

[Peter_SSs]

For equal to 11
=SORT(UNIQUE(FILTER($A$2:$A$33,COUNTIF($A$2:$A$33,$A$2:$A$33)=11)))

For less than 11
=SORT(UNIQUE(FILTER($A$2:$A$33,COUNTIF($A$2:$A$33,$A$2:$A$33)<11)))

Thank to Mh_Mike from another social media platform.

If that works for you then it looks like you need to update your version information as it would not work in Excel 2016.