I have a form where students can enter choices: for instance the specialisation they want to pursue, and the semester when they will start that specialisation.
An example entry form is here, but it has not much interest, other than add addition data to the example processing sheet. You can copy the sheet locally to edit it, and it will still pull the form data over.
I decided not to use the option on the form to allow only one entry per student as students often change their mind, make a new entry several months later. They were told that only their last entry will be considered at a specific deadline.
I wish to have some reporting in place that shows an overview of all choices, lists the students per specialisation etc... but only taking into account their last entry, and without deleting previous entries made.
To look up the last entry for a specific user, it is easy using:
query(importrange("1L3V0HIN69aVvCV-4Hx7m39w6zsuBZEaK2L0JDULP0NY","Form Responses 1!A:E"),"select Max(Col1), Col2, Col3, Col4 where Col2 contains '"&C1&"' group by Col2,Col3,Col4 order by Max(Col1) desc limit 1",0)
The e-mail (or part of it would be stored in C1). The trick is the limit 1 clause at the end combined with the order by clause.
But this no longer works when trying to filter for the whole cohort following a specialisation at a specific start date, or counting the amount of students per specialisation, etc.
This is where I could use some help. Maybe adding a column "outdated" and filling that in via an array formula could be an option, but I struggled with making that work on the original response spreadsheet.