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

#### Excelself

##### New Member
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

Omar

### 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
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
Excelself, Good afternoon.

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
Thank you. Will try it. Much appreciate it.

#### Excelself

##### New Member

it worked. Thank you so much!!

#### Marcílio_Lobão

##### Well-known Member
Excelself, Good morning.

Thanks for the feedback.

#### Excelself

##### New Member
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 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
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 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
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 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

#### Marcílio_Lobão

##### Well-known Member
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.

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...