I'm collecting repeated measures using Google Forms which only allows 1 or unlimited responses. I need a way to filter out responses that were entered by the same user too near to each other.
For example, if the below is the raw form responses, I'd want the query to exclude the final row since Jim had already responded on that day (or alternately keep his second response and exclude his first on 12/12).
| TimeStamp | UserID | SurveyData |
|----------------------|--------|------------|
| 12/03/2020 16:10:40 | Bob | Dog |
| 12/03/2020 16:10:20 | Sue | Cat |
| 12/03/2020 16:50:40 | Jim | Dog |
| 12/15/2020 16:10:40 | Bob | Cat |
| 12/14/2020 16:10:20 | Sue | Dog |
| 12/12/2020 16:50:40 | Jim | Cat |
| 12/12/2020 20:00:00 | Jim | Bird |
Should support 2000 unique users, each typically adding one record each month. Ideally, this could be written as a single query or filter but could use helper col counting number of entries for user on that day if that's easier. Would also be helpful if the exclusion criteria could be easily adjusted (e.g. changed to allow one entry per calendar week).
=filter
witharrayformula + countif(A:A,A:A)
and apply to column A something liketext(A1,"DD/MM/YYYY")
? – pjmg May 12 '20 at 14:05