One approach is to add a status column next to the data input: for example, in L10 it would be
=if(iserror(match(H10, H11:H, 0)), "current", "obsolete")
which returns "current" if no entry below the present row has the same email. Then your query can filter out the obsolete results.
The above does not work with arrayformula. I don't think there is an arrayformula-compatible approach, because the number of comparisons is quadratic in the size of array. Here is a script solution that auto-expands: put status(H10:H) in a new column, with the following function in a script.
function status(arr) {
flat = arr.reduce(function(a, b) { return a.concat(b); });
var output = [];
for (var i=0; i<flat.length; i++) {
if (flat[i] === '') {
output.push(['']);
}
else if (flat.slice(i+1).indexOf(flat[i]) === -1) {
output.push(['current']);
}
else {
output.push(['obsolete']);
}
}
return output;
}
The approach I took in this answer also works reasonably well here: use unique to get unique users (identified by emails), then filter rows by email, and use vlookup to pick the data just from the last row. The result will be a neat summary table that has only the latest data.
Using your example, I build such a table starting from the cell A20. In A20, I enter
=unique(H10:H)
obtaining the list of emails. Then in B20 I enter
=vlookup(999999, filter($G$10:$K, $H$10:$H=$A20), column()+1)
and extend this formula to the range B20:D. This is the result:
+-----------------------------+-----------------+--------+-----------------+
| peter.goedtkindt@school.edu | SpecializationB | 2016.2 | changed my mind |
| student1@school.edu | SpecializationB | 2016.1 | |
| studnent2@school.edu | SpecializationC | 2016.1 | |
+-----------------------------+-----------------+--------+-----------------+
(I don't know if you needed comments, but I included them anyway.) Using column()+1 as the last parameter of vlookup allows the formula to pick the relevant column, after vlookup with absurdly large search key 999999 zooms in on the last entry.
see cell E2 on your second tab where I entered:
=sort(ArrayFormula( {transpose(split(regexreplace(join(";", 'Form Responses'!C2:C), "(\w+),\s(\w+),\s", "$1, $2;"),";")),transpose(split(join("/", rept('Form Responses'!A2:A&"/", if(len('Form Responses'!C2:C), (len('Form Responses'!C2:C)-len(substitute('Form Responses'!C2:C, ",",""))+1)/2,)) ),"/"))}),1,1)
See if that helps ?
Best Answer
Credits: thanks to player0 for sharing the idea for a different problem
You can try this:
Spreadsheet solution: HERE