# Custom data transpose?

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 `VLOOKUP()` or `FILTER()` with `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.

Using your example, this formula can be used in row 2:

`=TRANSPOSE(FILTER(B\$2:B;MMULT(C\$2:E=B2;TRANSPOSE(SIGN(COLUMN(C\$2:E\$2))))))`

but will need to be filled down as far as is required. Hopefully you can adapt the references to your actual data, but comment back if this is an issue.

MMULT Usage

If you require a single formula that will automatically populate the entire data set, this can be done with native functions, but it will be horribly complex and quite inefficient. I would suggest a custom function if a single-formula solution was a requirement.

• Sweet, thank you for an answer AdamL. I was looking for a single formula but didnt specify such. I do not quite follow it well enough to adapt. If you look at the sample sheet, the formula continues some redundant - can you check it out and explain? Will this work with an expand() wrapper? – Tom Nov 26 '13 at 8:20
• You have inserted the formula in row 22, so the formula as it stands will not be referencing correctly. The formula in F22 should be `=TRANSPOSE(FILTER(B\$22:B;MMULT(C\$22:E=B22;TRANSPOSE(SIGN(COLUMN(C\$2:E\$2))))))`. – AdamL Nov 26 '13 at 21:30
• Finally figured out why I could not use AdamL's solution: Some of my cells in C\$2:E had trailing or leading whitespace that completely broke the formula. Once the whitespace was removed, it worked exactly as expected. Thanks AdamL. – Tom Jan 14 '14 at 5:33

I believe this can be done with custom function in Google docs. I have written a function that:

1. Scan the table for unique mentors and saves them into array
2. For every found mentor reads a student and saves him into array behind mentors name
3. Prints the data into new sheet (data is basically array of arrays)

So every row of original table can have different count of columns.

Prerequisites are:

• Original table with studens has to be on sheet called "students"
• Original table with studens has to have same layout as in the example (or you have to edit starting row and column for scanning in the code). There can't be any more information in the sheet or it will break the script.
• In the same spreadsheet, you have to create new a new clean sheet (call it "mentors" for example) and there will be the results printed.