If you add the following formula in cell D2:
=ARRAYFORMULA(FILTER(C2:C;C2:C<>"")*1.2)
then it will take on the complete column, via the array. The FILTER function, is to make sure no empty cells are taken into account.
I've added the result into the file you shared with us.
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
The problem was due to Google Forms adding a new row every time.
If you are having the same problem here's what you need to do to fix it:
Where you previously had your formula change it to:
Change the sheet name, and enter the cell you want to target, as-well as the column.
Make sure you delete all other formulas in that column.
This will automatically post the new data from the cell into the one below it. Just make sure you have no other formulas in the column. Also make sure the cell you target is the one where the form data is stored.