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

Excelself

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

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
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

Well-known Member
Joined
Oct 7, 2013
Messages
697
Platform
  1. Windows
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.
 

Excelself

New Member
Joined
Mar 16, 2018
Messages
20

ADVERTISEMENT

it worked. Thank you so much!!
 

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
697
Platform
  1. Windows
Excelself, Good morning.

Glad to have helped you and your school.

Thanks for the feedback.
 

Excelself

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

Well-known Member
Joined
Oct 7, 2013
Messages
697
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,333
Messages
5,528,090
Members
409,801
Latest member
Jamaira

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top