7

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.

4
+25

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
0

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.

See my sample spreadsheet where I tested it: https://docs.google.com/spreadsheet/ccc?key=0AtL0o7PQ5KfldGk3TVp3U3ZkcVF3YUl6UFhxS0pZT2c&usp=drive_web#gid=0

Sadly, Google took some time to review my code, so I cannot share it with you easily on Google Apps Script. But you can copy it here from pastebin: http://pastebin.com/0hFMhC8e and create your own custom function. Just go to your spreadsheet (with the data), create a new clean sheet, go to Tools > Script Manager > New, in the new window choose from list on the left Spreadsheet, delete the sample code, copy and paste my code from pastebin, name your new function "transposeColumns" and save it. Then go to the widow/tab with the spreadsheet and the script manager opened. Hit Reload button in the manager and you should see there you new function. So then just choose it and hit Run. It will ask you for permission for the script to access the Google Drive API. Submit it. Now it should take the data from the sheet called "students" automatically and transpose them to current active sheet. Maybe try to re-run the script if it does nothing after the permission submit.

There some troubles like: What about the student IDs - that could be easily added into the script to copy them also. The script itself was written in JavaScript as simple as possilbe. Could be optimilized I believe.

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.