I have an export for a group that composed of student/mentors. Everyone in the group is both a student and a mentor. I need to transform the data to have mentors with assigned students rather than students with assigned mentors.
The software gives me an export such as five columns in a sheet that has student names and mentors that have been assigned to that student:
ID Numbers of Students | Students | Mentor 1 | Mentor 2 | Mentor 3
I need a sheet to reference the data with mentors in one column and corresponding students such as:
ID Numbers of Mentors | Mentors | Student 1 | Student 2 | Student 3 | Student 4
Students are mentors and mentors are students - it is the same set of names. Mentors have a max of four assigned students. Only one row per Mentor.
I am thinking
QUERY() or perhaps
ARRAYFORMULA() but I have not been able to assigned use
ARRAYFORMULA(). I am not getting anywhere with it.
What Google Spreadsheet formula do I use to transpose the data as described? Can you provide an example? I will accept a custom function, but surly this can be done with existing tools.