Remove duplicate Google Form™ submissions, keeping the latest
People submit twice: a double click, or a second go with a corrected answer. Here is how to keep one row per person, the latest, without deleting anything from the responses tab.
Why the built-in tool is the wrong one here
Data › Data cleanup › Remove duplicates looks like the answer, but for form responses it gets three things wrong:
- Every row has a different timestamp. Include the timestamp column and nothing is a duplicate. Leave it out and you have to choose which columns count.
- It keeps the first copy. When someone resubmits to correct an answer, the later row is the one you want.
- It deletes rows from the responses tab. Those rows are gone from the sheet, but the form still has them. Your sheet and the form's own summary now disagree, and there is no record of what was removed.
Decide what makes two submissions “the same”
- Email, if the form collects email addresses. This is the most reliable key.
- Name plus something else (name and date of birth, name and team), if it doesn't. Name alone merges two different people called Sam.
- The whole row, for double clicks. Identical answers seconds apart are one submission.
If people type their email address, tidy it first. Sam@Example.com and
sam@example.com are the same person. On your working tab, add a helper column
with =LOWER(TRIM(B2)) and use that as the key.
Keep the latest submission per email
On a new tab, with the timestamp in column A and the email in column B of the responses tab:
=SORTN(
SORT(
FILTER('Form Responses 1'!A2:F, 'Form Responses 1'!A2:A <> ""),
1, FALSE),
9^9, 2, 2, TRUE)Read it from the inside out:
FILTERdrops the empty rows below the last response.SORT(…, 1, FALSE)puts the newest response first.-
SORTN(…, 9^9, 2, 2, TRUE)sorts by column 2 (the email). Tie mode2keeps only the first row of each tie, which after step 2 is the newest.9^9just means “no row limit”.
To keep the first submission instead, as you would for
first-come-first-served bookings, change FALSE to TRUE in the
SORT.
See which rows were dropped
Removing rows you can't see is how mistakes go unnoticed. On a copy of the responses sorted oldest first, a running count flags every repeat:
=COUNTIF($B$2:B2, B2)Anything above 1 is a repeat of an earlier row. Filter on it and read through the list before you trust the de-duplicated tab. It will catch the two different people who shared one email address.
Stopping duplicates at the form
In the form's Settings › Responses, “Limit to 1 response” prevents second submissions. It also makes everyone sign in to a Google account, which will turn some people away. “Allow response editing” lets people correct an answer without submitting again. Both cut duplicates down; neither removes the ones you already have.
The one-click version
The add-on matches duplicates on email when the form collects one, or on columns you pick. It keeps the latest or the first, and moves the rest to an Excluded tab with the row each one duplicates. Identical double-click submissions a few seconds apart are always caught.
Form Response Cleaner is a Google Sheets™ add-on: free for up to 250 responses, and your responses tab is never touched.
More on Google Form™ responses
- How to split Google Form™ checkbox responses into separate columns
- Count checkbox answers from a Google Form™ (COUNTIF on multi-select)
- Google Form™ responses: shorten long question headers without breaking the form
- Phone numbers losing the leading zero in Google Form™ responses
- Why you shouldn't sort or edit the Form Responses tab (and what to do instead)
- The guide: every rule, with the by-hand formula
Google, Google Forms™, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. Form Response Cleaner is not affiliated with or endorsed by Google.