Multiple Results From Same Data That Changes Daily

[BradHedinger]

My data set is large.  Approximately 55,000 Rows and 8 Columns.  I have truncated the report for the sake of the attachment and removed/replaced sensitive information.  Definitions of data in the attached report is listed at the bottom of this post.

I want to identify separate groups of StuNums (Students) that meet varying conditional criteria so that I can make sure to send appropriate messaging to each group of students. 

Student Groups Criteria:

1. If a student is scheduled for only 1 CourseCode in any TermCode, (could be multiple TermCodes), and has a LetterGrade of W, WP, or WF for that 1 Course Code and TermCode.
2. If a student is scheduled for multiple CourseCodes in any TermCode, (could be multiple TermCodes and has a LetterGrade of W, WP, or WF for 1 or more CourseCodes but is still scheduled for 1 or more other CourseCodes.

I have tried keeping it simple with a Pivot Table, and Slicers.  Then have gone so far as to use PowerPivot and PowerQuery.  I am convinced that this is not as complicated as I have made it to be at this point.  Hoping a fresh set of eyes can help. 

Definitions of data in attached report.

1. Column A:  Course Code.  The code of the course that the student is scheduled to take.  
2. Column B:  EnrollStatus.  The student’s status for that specific Course Code. 
3. Column C:  Letter Grade.  The students letter grade they earned in that specific course.  However, blank cells mean the student is still in the course, so grades have not yet posted.  A letter grade of W, WF, or WP means they withdrew from that course.
4. Column D:  StatusDescrip.  The student’s status in school.  Active means they are in school taking classes  Enrolled Ready To Access means they about to start school or are in their first month.
5. Column E:  StuNum.  This is the unique ID# of each student.  I replaced real ID#'s with generic ones for the sake of the attachment.
6. Column F:  TermCode.  This is the alphanumeric code that indicates what month of school the specific student is taking the specific course on that row.
7. Column G:  Creditsattempt.  The credit value of the specific course the specific student is taking in the specific TermCode on that row.
8. Column H:  CreditsEarned.  The credits earned by the specific student in the specific course in the specific TermCode on that row.

[#2 · Accepted solution]

@BradHedinger 

Given all that you've tried already, you clearly are something of an Excel power user. On that basis, I'm going to assume that just pointing you in another direction--one you didn't mention--might suffice, and give you the opportunity to say "I did it myself."

You don't mention the newest Dynamic Array functions--UNIQUE, FILTER, SORT--so (assuming you have the most recent release of Excel), let me introduce you to them by means of the attached video, which happens to be how I became acquainted with them late last year. I don't know for sure that they'll serve your need, but I think they will, and your more intimate knowledge with those criteria will serve you in good stead in forming the criteria section of FILTER.

Here's the video: https://www.youtube.com/watch?v=9I9DtFOVPIg

And here's another resource with examples and further references on constructing the criteria using boolean expressions: https://exceljet.net/excel-functions/excel-filter-function

[#3]

Thankyou very much! Incredibly eye-opening. Hopefully the solution lies within!

[#4]

How can they have a grade if they're only scheduled and not active, so you're really looking at comparing two lists, List#1 is who is actively enrolled with letter grades of W, WF and WP that have not scheduled to re-enroll

[#5]

@Yea_So Great question!  This is because at any time, there are students whose schedules are being edited/changed for a variety of reasons.  Also, some courses grades post before/after others.  So many moving pieces.