I have a shared sheet where I have people list their classes by priority like below:

StudentA  StudentB  StudentC  StudentD
Math      Math      Sci       Eng
Sci       Eng       Eng       Math
Eng       Sci       Math      Sci

And I have a second table which I am currently making manually based on the above data. I would like to ask if this can be somehow automated via formula/function to save me the time (as in reality I am dealing with way more students and classes).

There may also be times where a student does not select from all the class options (it's provided in a dropdown) and only has a few on their column.

Math                 Eng                 Sci
StudentA/StudentB    StudentD            StudentC
StudentD             StudentB/StudentC   StudentA
StudentC             StudentA            StudentB/StudentD

You have to reorder the starting data as follows :

enter image description here

Then use a formula with a filter and expand :


enter image description here

Sheet example


While this isn't exactly the goal format you mentioned, i actually think it might be easier to keep track.

Here is a sample sheet.

This formula you'll find once for each class. it will build the whole class in order of student priority with their priority numbers to the left of the name.

=ARRAYFORMULA(QUERY(SPLIT(TRANSPOSE(SPLIT(TEXTJOIN("#",TRUE,IF('By Student'!$B2:$E<>"",ROW('By Student'!$B2:$E)-1&"|"&'By Student'!$B1:$E1&"|"&'By Student'!$B2:$E,)),"#")),"|"),"select Col1,Col2 where Col3='"&A1&"' order by Col1"))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.