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