Sorting data in Google Spreadsheet by cell entry

Question

I have a Google Spreadsheet that lists various events. Students have entered their name next to events that they would like to sign up for. It looks like this:

Each student may sign up for more than one event.

I would like to automatically generate a second sheet in the spreadsheet that lists the students alphabetically and lists next to them the events they have signed up for. Something like this:

I can generate the ordered list of names using SORT(UNIQUE()) but I'm having a lot of trouble getting the associated events to display. I thought HLOOKUP or QUERY or a combination of IFs and ORs or maybe some sort of scripting would work, but I haven't had any success.

Answer 1

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);
}

Answer 2

Put this under Tools >> Script editor, then Save. You'll have to change the sheet names and ranges to match what you have.

// Every time you make an edit to the ss, onEdit runs
function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Sheet1'); // input
  var s2 = ss.getSheetByName('Sheet2');  // output
  var events = s.getRange('A2:A').getValues();
  var students = s.getRange('B2:E').getValues();
  var studentList = [];
  var eventList = [];
  var cell = s2.getRange('B2'); 

  // Look through all the students who signed up
  for (var row in students)
    for (var col in students[row])
      if (students[row][col] != '') {
        studentList.push(students[row][col]);
        eventList.push([students[row][col], events[row][0]]);
      }

  // Just give me uniques
  var unique=studentList.filter(function(itm,i,studentList){
    return i==studentList.indexOf(itm);
    });

  unique.sort();

  // Write all the unique names out in 1st column and then each event for each student
  for (var s in unique) {
    var counter = 1;  // for the column offset
    cell.offset(s, 0).setValue(unique[s]);
    for (var e in eventList)
      if (eventList[e][0] == unique[s]) {
        cell.offset(s, counter).setValue(eventList[e][1]);
        counter++;
      }
  }
};

Answer 3

Why go through all this trouble, creating this script, as there's a good alternative present within google spreadsheets:

TRANSPOSE

See example file I prepared: application file (editable)

This gives the same structural information, presented differently. I've added an extra check to it as well (see sheet applicants):

=IF(COUNTA(B2:E2) >
 COUNTA(UNIQUE({Applicants!B2;Applicants!C2;Applicants!D2;Applicants!E2}));
"Invalid";"OK")

If you add new courses, then be sure to copy the check formula (if you want to) and summation formula (for total attendees).

PS: I've tried the pivot function as well but it didn't give good results.