6

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:

enter image description here

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:

enter image description here

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.

4
  • I've gotten closer using commands like =filter('By Event Name'!$A$3:$A$28;'By Event Name'!B$3:B$28=$B2) ("By Event Name" is the name of the first sheet) and then using conditional formatting to black out #N/A. This misses instances where a student has two entries in the same column in the first sheet.
    – Noah
    Nov 4, 2012 at 16:13
  • 1
    Why didn't you create a Google form for it? So everyone would just fill up a form and your resulting table would actually look like your end result. Unless you have particular limitations on number of people per project... Nov 6, 2012 at 11:22
  • I need both views - by event and by student - available. There is a limit on the number of people per project, and since students work in groups, they want to see what other students' choices are.
    – Noah
    Nov 7, 2012 at 2:55
  • they want to see what other students' choices are - maybe that's a downside because people make their preference based on people instead of pure interest. But I understand the need of both. Nov 7, 2012 at 9:31

3 Answers 3

2
+50

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

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++;
      }
  }
};
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.