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.
Best Answer
This is not possible with a a single form. One alternative is to have two copies of your quiz a limit both of them to allow only one submission for each respondent.