Google Sheets – Custom Data Transpose Methods

google sheetsgoogle-apps-script

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.

Best Answer

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.

Related Solutions

Google Sheets – Sorting Data by Cell Entry

Here's an Apps Script that will build a second sheet based on the first (javascript-only approach).

I only tested it on a simple 3x5 version of your first set of data, with row 1 (array index 0) being the headers.

It fails if the second sheet already exists - it's not pretty. But it, and the Apps-script tutorials, should get you started.

function buildSheet2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var students = {};  // for a map of students indexed by name, mapping to a string of events separated by "|"
  for(var i=1; i<data.length; i++){
    var eventName = data[i][0];
    Logger.log(eventName);

    for (var j=1; j<3; j++){
      var studentName = data[i][j];
      if (studentName != "") {
        if (!(studentName in students)) {
          students[studentName] = eventName;
        } else {
          students[studentName] = students[studentName] + '|' + eventName;
        }
      }
    }
  }
  Logger.log(students);
  // insert a new sheet and make it active  
  var sNameSheet = SpreadsheetApp.getActive().insertSheet("By student name");
  SpreadsheetApp.setActiveSheet(sNameSheet);
  var newData = [["Name", "Event", "Event", "Event"]];
  var studentKeys = Object.keys(students);
  // split the events for each student into columns
  for (s in studentKeys) {
    var newRow = ["Name", "Event", "Event", "Event"];
    Logger.log (studentKeys[s]);
    newRow[0] = studentKeys[s];
    // clear out temp row
    for (i=1; i<newRow.length; i++) {
      newRow[i] = "";
    }
    var events = students[studentKeys[s]].split("|");
    Logger.log(events);
    for (i=0; i<events.length; i++) {
      newRow[i+1] = events[i];
    }
    newData.push(newRow);
    Logger.log(newRow);
  }
  Logger.log(newData);
  // write data to new sheet
  sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

Google Sheets – View and Edit Data in a Form

At this time, Google docs editors don't include a built-in feature but you could extend them through add-ons and Google Apps Script. But someone already did the hard work for us and developed a free Google Gadget that work together with Google Apps Script to use

Google Forms as the UI for creating and editing records
Google Sheets for storing records
Google Gadgets as the list/search UI

From Awesome Tables

The "Awesome Tables" gadget can be used to create a table from a spreadsheet and add interactive controls to manipulate the data it displays. It is not an Apps Script web app but a Google Gadget and it uses the Google Visualization API.

From Awesome Table and Google Form

Awesome Table is mainly used to display data in multiple ways. But it can also be very useful to let your users edit the data displayed. Here's how to do it, using Google Forms : see the documentation (or take a look at the spreadsheet).

If you try it, note that the changes you'll make can take a minute before being displayed on the site.

Best Answer

Related Solutions

Google Sheets – Sorting Data by Cell Entry

Google Sheets – View and Edit Data in a Form

Related Topic