2

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.

2

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.

  • Thanks Normal Human ! Would these function fit in an array formula so that they are auto-filled in ? This is where I struggled with – Generic IT Account Aug 22 '15 at 15:43
  • I added your solution to the sheet - I moved the intermediate table out of the way to prevent overwriting of data if new entries are made, so the formula's changed slightly pointing to other columns, but apart from that all is the same. As for the array formula : it would be nice to have, but I could drag the vlookup formula's down a few thousand rows so that the form would still work until my retirement :-) Thanks for your help - again! Peter.Goedtkindt – Generic IT Account Aug 22 '15 at 16:58
  • I added a script-based solution that does not require dragging. I don't think an arrayformula solution exists because the process involves comparing each row to the rest of rows below it. arrayformulas work for in situations where each row is processed on its own. – user79865 Aug 22 '15 at 17:02
  • I think I need a bit more info. Who calls the script ? and what's the input and output: Is this triggered each time a form is filled in ? Do you have an example of the implementation? I found out that scripts that take inputs from dynamic (e.g. form generated data) fail to run automatically when inserted as a formula in a sheet... so I wonder if that would be the case too here? – Generic IT Account Aug 22 '15 at 18:33
  • This is not a triggered script, but a custom function. As such, it should recalculate every time the range it references (H10:H) changes. (But I recommend testing this.) The output is a column with a word "obsolete" or "current", In your spreadsheet, enter =status(H10:H) in L10 so that the output is aligned with the data. – user79865 Aug 22 '15 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.