Need help in finding a formula for the school i work for

[Excelself]

I need a formula in excel to have a worksheet showing that a student has completed all 9 courses as indicated in the database shown below. The original database has thousands of rows. Notice that ID # are repeated. If there was one ID# and if each course was in separate column, I would have used VLOOKUP formula. i need the worksheet to show the following:

ID Student name Has student completed all 300 series classes?
1663727 Ahmedin, Shifa O YES
1625672 Alderete, Bobby M NO


1663727 Ahmedin, Shifa O 307 Bus. Etiq. IT-R6
1663727 Ahmedin, Shifa O 301 Wkplce Harass-YH
1663727 Ahmedin, Shifa O 310 Wellness 3.17
1663727 Ahmedin, Shifa O 303 Eff Rsm Wrtng-YH
1663727 Ahmedin, Shifa O 302 Adv Pers Fin-YH
1663727 Ahmedin, Shifa O 306 Ind Liv Skill-YH
1663727 Ahmedin, Shifa O 311 CBL/IP IT R2/R4
1663727 Ahmedin, Shifa O 304 Time Mgmnt-YH
1663727 Ahmedin, Shifa O 308 Adv in Wkplce-YH
1686047 Alatorre, Perla A 307 Bus. Etiq. IT-R6
1625672 Alderete, Bobby M 307 Bus. Etiq. IT-R6
1625672 Alderete, Bobby M 301 Wkplce Harass-YH
1625672 Alderete, Bobby M 310 Wellness 3.17
1625672 Alderete, Bobby M 303 Eff Rsm Wrtng-YH
1625672 Alderete, Bobby M 302 Adv Pers Fin-YH
1625672 Alderete, Bobby M 306 Ind Liv Skill-YH
1625672 Alderete, Bobby M 311 CBL/IP IT R2/R4
1625672 Alderete, Bobby M 304 Time Mgmnt-YH


Thank you for your time

Omar

 

[RCBricker]

if the data is in columns as below:

Column A = student ID
Column B = Student name
Column C = Class

then you could use something like the following formula in column D, or if you have a list of students you could use this in the column next to the list.

=IF(COUNTIF(A:A,A1)>=9,"Yes","No")

this is based on student ID.

 

[Marcílio_Lobão]

Excelself, Good afternoon.

I feel glad to help your school.

Scenario:

A2:A1000 --> ID #
B2:B1000 --> Student Name
C2:C1000 --> Class

Report:

F2:F1000 --> ID # --> FORMULA -->

IFERROR(INDEX($A$2:$A$1000,MATCH(0,INDEX(COUNTIF($F$1:$F1,$A$2:$A$1000),0),0)),"")

G2:G1000 --> Student Name --> FORMULA -->

=IFERROR(INDEX($B$2:$B$1000,MATCH(F2,$A$2:$A$1000,0)),"")

H2:H1000 --> STATUS --> FORMULA -->

=IF(F2="","",IF(COUNTIF($A$2:$A$1000,F2)=9,"Yes","No"))

I did an little example for you:
https://www.sendspace.com/file/dozosn

Is that what you want?

I hope it helps.

 

[Excelself]

Thank you. Will try it. Much appreciate it.

 

[Excelself]

it worked. Thank you so much!!

 

[Marcílio_Lobão]

Excelself, Good morning.

Glad to have helped you and your school.

Thanks for the feedback.

 

[Excelself]

Marcilio,

I need your help again. Maybe I failed to explain what I really wanted. What I want the program do is to tell me if a student has completed all required 300 series courses as indicated in column 4. Once a student has a total of 9 completed courses, I want a "Yes" displayed under the column "Course Completed". I think here, it is displaying a "Yes" once the count of the same ID# reaches 9. "Yes" is displayed whether the course status is complete, incomplete, or currently enrolled.
I am almost there. Sorry for the misunderstanding. Thank you.

StudId StudName CourseName Course status ID Name Course complete?
1653488 Abrego Padron, Luis G 307 Bus. Etiq. IT-R6 Completed 1653488 Abrego Padron, Luis G Yes
1653488 Abrego Padron, Luis G 301 Wkplce Harass-YH Completed 1670586 Adams, Alexander L Yes
1653488 Abrego Padron, Luis G 310 Wellness 3.17 Completed 1671267 Allen, Ladaisha D Yes
1653488 Abrego Padron, Luis G 303 Eff Rsm Wrtng-YH Completed
1653488 Abrego Padron, Luis G 302 Adv Pers Fin-YH Completed
1653488 Abrego Padron, Luis G 306 Ind Liv Skill-YH Completed
1653488 Abrego Padron, Luis G 311 CBL/IP IT R2/R4 Completed
1653488 Abrego Padron, Luis G 304 Time Mgmnt-YH Completed
1653488 Abrego Padron, Luis G 308 Adv in Wkplce-YH Completed
1670586 Adams, Alexander L 308 Adv in Wkplce-YH Currently Enrolled
1670586 Adams, Alexander L 301 Wkplce Harass-YH Currently Enrolled
1670586 Adams, Alexander L 310 Wellness 3.17 Currently Enrolled
1670586 Adams, Alexander L 303 Eff Rsm Wrtng-YH Currently Enrolled
1670586 Adams, Alexander L 302 Adv Pers Fin-YH Currently Enrolled
1670586 Adams, Alexander L 306 Ind Liv Skill-YH Currently Enrolled
1670586 Adams, Alexander L 311 CBL/IP IT R2/R4 Currently Enrolled
1670586 Adams, Alexander L 304 Time Mgmnt-YH Currently Enrolled
1670586 Adams, Alexander L 308 Adv in Wkplce-YH Currently Enrolled
1671267 Allen, Ladaisha D 307 Bus. Etiq. IT-R6 Incomplete
1671267 Allen, Ladaisha D 301 Wkplce Harass-YH Incomplete
1671267 Allen, Ladaisha D 310 Wellness 3.17 Incomplete
1671267 Allen, Ladaisha D 303 Eff Rsm Wrtng-YH Incomplete
1671267 Allen, Ladaisha D 302 Adv Pers Fin-YH Incomplete
1671267 Allen, Ladaisha D 306 Ind Liv Skill-YH Incomplete
1671267 Allen, Ladaisha D 311 CBL/IP IT R2/R4 Incomplete
1671267 Allen, Ladaisha D 304 Time Mgmnt-YH Incomplete
1671267 Allen, Ladaisha D 308 Adv in Wkplce-YH Incomplete

 

[Marcílio_Lobão]

Excelself, Good morning.

"...Maybe I failed to explain what I really wanted..."
You're absolutely right.
But no problems.

The formulas have been developed following exactly your information.

You did not say anything about a column containing the status of the course and that this information would be the main criterion.

Following the description of your new need, simply modify the formula in column H.

H2:H1000 --> STATUS --> FORMULA -->

Before:

=IF(F2="","",IF(COUNTIF($A$2:$A$1000,F2)=9,"Yes","No"))

Now:

=IF(F2="","",IF(COUNTIF[B][COLOR=#ff0000]S[/COLOR][/B]($A$2:$A$1000,F2[B][COLOR=#ff0000],$D$2:$D$1000,"Completed"[/COLOR][/B])=9,"Yes","No"))

I also made a new model for you.
https://www.sendspace.com/file/4tk2hh

Please, tell us if this is what you need.

I hope I've helped you and your school.