Huge disclaimer: I’m no Excel/Sheets master so there is probably a much better and more efficient way of doing things but this is just an extremely hacky thing I threw together one afternoon. So if you know of any alternatives, I’d be happy to hear it. Also I am not a teacher but I’ve renamed a few things to try and simplify things.
The setup
Class Sheet, Table 1 – also known as the “input table” where the first row lists each Student who will be selecting their class choices in order of priority (highest to low) via dropdowns. The dropdown is from the built-in data validation feature and uses Reference!A1:A to pull the class choices. Once filled in, the table looks like below:
| Student 1 | Student 2 | Student 3 |
| English | English | Math |
| Science | Math | English |
| Math | Science |
Reference Sheet, Table 1 – contains just a single column of the different classes available as an option.
The goal
What I wanted to automate was a table that would be based on the data from the input table that showed each class option and under it the list of students according to the priority they chose for that class., with the following extra rules:
- If two students put the same priority on the same class, they would be listed in the same cell.
- If the student did not choose the class in their dropdown, their name would simply not be listed for that class column.
- This table would auto-update itself if any changes were made to the input table and re-sort the priority list accordingly.
So in this case, the Class Sheet, Table 2 (“Sorted table”) would provide the following:
| English | Math | Science |
| Student 1 / Student 2 | Student 3 | Student 1 |
| Student 3 | Student 2 | Student 2 |
| Student 1 |
Hacking it together
The first thing I did was create a new Priority Calc sheet in case I messed up anything to keep the original Class Sheet from getting cluttered. Then for each class, I needed three columns. Then the number of rows for each class would depend on the total number of students.
| English | student | rank |
| [1] | [2] | [3] |
[1]: The first column is a manual check using MATCH that goes through each student’s column in the input table and checks to see if the class was listed, then returns the row number it was found in. The formula is then wrapped in IFERROR so that if the class was not found by MATCH, it returns 999. The row number (or 999) will be used later by [3]. For this column, the final formula is =IFERROR(MATCH(A1,'Class Sheet'!A2:A4,0),999)
[2]: The second column is a simple check based on [1] where if a student listed the class in their priority sheet (based on whether a number other than 999 is listed), then the column would simply display their name. The formula here is just =IF(A2<999,'Class Sheet'!A1,"n/a")
[3]: The third column would be the actual ranking system. Initially I tested with the built-in functions of RANK and RANK.EQ but the problem is that if there are multiple students with the same priority, it affects the rank number following it. Fortunately, I was able to use a reversed version of the formula used in this Excel article. In this case, my formula for the third column is =SUMPRODUCT((A2>=A2:A4)/COUNTIF(A2:A4,A2:A4))
With the above setup, my English table then results in:
| English | student | rank |
| 1 | Student 1 | 1 |
| 1 | Student 2 | 1 |
| 2 | Student 3 | 2 |
Finally, we go back to the sorted table in the Class Sheet.
Each class would have its own column and the number of rows would be equivalent to the highest possible rank (which is essentially the total number of students, assuming each one selected a different priority for each class, as in the case of Math in my sample table above).
The formula is =IFERROR(JOIN(" / ",FILTER('Priority Calc'!B2:B4,'Priority Calc'!C2:C4 = 1))) Each row formula uses the FILTER function to check the Priority Calc sheet for any rows with the appropriate ranking (in this case, 1 as indicated by the = 1 at the end of the formula). For multiple results, it then joins them together with ” / “. Repeat with slightly modified formula for each.
Any priority rows where a student did not select the class returns “n/a” as per the formula from [2]. My way of quickly cleaning that up visually was to simply add a conditional formatting rule that changes the text color of the cell to the same as the background if it contains “n/a” (as you would never have a student name joined with an “n/a” result). The alternative would probably mean looking into how to stop the JOIN function from running if it detects an “n/a” in the filter result.
If I have time to refine this in the future and make it quicker to add new classes to the ranking calculation, I might look into it but for now, it works.