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

Excelself

New Member
Joined
Mar 16, 2018
Messages
22
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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.
 
Upvote 0
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 -->
Code:
IFERROR(INDEX($A$2:$A$1000,MATCH(0,INDEX(COUNTIF($F$1:$F1,$A$2:$A$1000),0),0)),"")
G2:G1000 --> Student Name --> FORMULA -->
Code:
=IFERROR(INDEX($B$2:$B$1000,MATCH(F2,$A$2:$A$1000,0)),"")
H2:H1000 --> STATUS --> FORMULA -->
Code:
=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.
 
Upvote 0
Excelself, Good morning.

Glad to have helped you and your school.

Thanks for the feedback.
 
Upvote 0
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
 
Upvote 0
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:
Code:
=IF(F2="","",IF(COUNTIF($A$2:$A$1000,F2)=9,"Yes","No"))

Now:
Code:
=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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top