Students are required to complete 4 courses. My query pulls all students by admit term and only includes the courses they've completed. How do I determine which student is missing one or more of the 4 courses?
Need to pull which students do not have all four courses listed.
STUDENT ID NAME SUBJECT COURSE 1010293 Smith, Joe ENGL 10803 1010293 Smith, Joe MATH 10283 1039283 Todd, Don ENGL 10803 1039283 Todd, Don MATH 10283 1039283 Todd, Don ECON 10233 1767289 Park, Kim ENGL 10803 1767289 Park, Kim MATH 10283 1767289 Park, Kim ECON 10223 1767289 Park, Kim ECON 10233 V Look Up ID# 1010293 Name Smith, Joe ENGL 10803 complete ECON 10223 missing ECON 10233 missing MATH 10283 complete
There are lots of ways to do what you want. What seems most straightforward to me, is to create a matrix on a second worksheet. You can use the following steps.
Open a new worksheet. The first row could have the possible headers:
Student No | Student Name | Engl 10803 | Math 10283 | Econ 10223 | Econ 10233
Copy the list of students' ids in
Column A. Highlight the pasted ids and clear duplicate numbers by using Excel's remove duplicate function.
Data>Data Tools>Remove Duplicates
VLOOKUP if you want to pull in student names. something like
B2 of your new sheet.
IF array function to determine if those students have taken those classes. Based on your test data, the following function appears to work.
This is an array function, so copy and paste it into
C2, then submit the function by using
ctrl+shift+enter. Then you can copy and paste that cells into the range of students
C2:F4 on your test data.
This function works because
Sheet1!$C$2:$C$10&" "&Sheet1!$D$2:$D$10 returns the subject and course number in the same format as the column headers of the second pages.
Home>Styles>Format as Tableor
Data>Sort and Filter>Filter