0

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).

2
  • I thought I had a solution by adding a column for =ISOWEEKNUM and then using =SORTN with tie mode 2 (remove duplicates). Seemed to work but wasn't robust (the way I did it). – user2767078 May 12 '20 at 8:44
  • Did you try a =filter with arrayformula + countif(A:A,A:A) and apply to column A something like text(A1,"DD/MM/YYYY") ? – pjmg May 12 '20 at 14:05
1

To limit to 1 per week: Create a helper column D that calculates the week of the year:

=ISOWEEKNUM( A2 )

      A                   B           C          D
| TimeStamp            | UserID | SurveyData | Week
|----------------------|--------|------------|------
| 12/03/2020 16:10:40  | Bob    | Dog        |11
| 12/03/2020 16:10:20  | Sue    | Cat        |11
| 12/03/2020 16:50:40  | Jim    | Dog        |11
| 16/03/2020 16:10:40  | Bob    | Cat        |12
| 16/03/2020 16:10:20  | Sue    | Dog        |11
| 16/03/2020 16:50:40  | Jim    | Cat        |12
| 17/03/2020 20:00:00  | Jim    | Bird       |12

Use to SORTN filter out any duplicates by the same person occurring in the same week. (In this case, only dropping the last row.)

=SORTN(A2:D,9^9,2,COLUMN(B1),1,COLUMN(D1)

Explanation: SORTN (range, max number of results to return (a big #), tiebreaker type (remove duplicates), First column to sort by, sort order, second column to sort by if first was a tie, sort order.

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.