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.
Currently there is no real way to do this. The only thing I could suggest is a list of check boxes with numeric input fields correlating with each box, with the hope that the user won't accidentally or purposefully enter bad data into the numeric fields corresponding to book he didn't read.
If you are making this for your own, self-hosted sited. I'd look into something like RS Forms Pro, which makes creating forms and manipulating responses much faster and easier.
Best Answer
Something I have done to accomplish this(sort of) is to make a certain number of identical questions. It could be 5 or 10 or whatever you see the max number being. Then I add a question at the end of each of those that asks if the person would like to add another.
So, in your case, you would have Book 1, Book 2, and Book 3. All are separate sections in the form. The person fills out Book 1 recommendation and says yes to adding another. They get routed to Book 2 section. They only have 2 recommendations so they answer no to adding another. They then get routed to the final section in the form which just contains a submit button.
I wish the ability to just add another question like you are asking for existed but until Google adds something like that, this should get the job done for you.