Duplicate scans come from three different places, and only one of them is a mistake. A code sitting in front of the camera gets read again as a phone passes over it. A person is not sure the first scan registered and scans again. And sometimes a person or an item genuinely comes back: a second entry after lunch, a tool going out twice in one day, two units of the same product on a shelf.
Those three need three different answers, and at the moment of scanning nothing can reliably tell them apart. That is why the useful question is not how to stop duplicates, but where to decide what they mean. The answer this guide argues for: record everything, flag the repeat at the time, and make the decision in the spreadsheet.
Written by QR to Sheets, so read the product section as vendor material. The formulas below work on any sheet of scan rows, whichever tool wrote them, including a Google Form's response sheet.
Why refusing a repeat scan is the wrong default
A system that silently rejects the second scan feels tidy, and it destroys information. Afterwards you cannot tell a person who scanned once from a person whose second scan was thrown away, and you cannot recover the genuine second trip that the rule happened to swallow. If a record is ever disputed, a complete log is evidence; a log with rejected rows missing is an argument.
Recording and flagging keeps the log complete and moves the judgement to the one place it can be changed later without losing anything. If you decide next month that a second scan within ten minutes should count as the same visit, you change a formula. If the scanner had refused it, that data would simply not exist.
The free route: a Google Form, then formulas
A Google Form behind a printed QR code writes every submission to a linked Google Sheet, duplicates included. That is the right behaviour for the log, and it costs nothing. The form's Limit to 1 response setting is not a duplicate filter for this job: Google's own help states that it requires respondents to sign in to their Google Account, so it limits accounts rather than codes and adds a sign-in step to every scan.
Formulas that build a deduplicated tab
These assume the timestamp is in column A and the scanned value in column B, which is the default QR to Sheets row and close to most form layouts. Adjust the letters to your own sheet, and make sure the timestamp column holds real dates rather than text, or every date comparison below silently fails.
- Each distinct code once: =UNIQUE(B2:B). Fast, but it drops the timestamp, so it answers who and never when.
- The date of each scan, in a helper column G: =IF(A2="", "", INT(A2)), formatted as a date.
- A first-scan flag per code per day, in column H: =COUNTIFS($B$2:B2, B2, $G$2:G2, G2)=1. It is TRUE on the first scan of that code on that date and FALSE on every repeat, because rows arrive in time order.
- The deduplicated register on its own tab: =FILTER(Log!A2:H, Log!H2:H=TRUE).
- The earliest scan of each code on each day, without helper columns: =QUERY(Log!A2:B, "select B, toDate(A), min(A) where B is not null group by B, toDate(A)", 0).
- A visual check on the raw log: a conditional formatting rule with the custom formula =COUNTIF($B:$B, $B2)>1 shades every value that appears more than once.
Google Sheets also has Data, Data cleanup, Remove duplicates. It deletes the repeated rows from the range you select, so it belongs on a copy of the data, never on the tab your scans land in.
Where the free route breaks
- Nothing warns the person at the door. A repeat is discovered at the weekly reconciliation, when it can no longer be asked about.
- Typed values create near-duplicates. A-102, a102 and A-102 with a trailing space are three different values to a formula; wrap the column in UPPER(TRIM()) before you compare, or scan instead of typing.
- A submission attempted with no signal is lost, and people who are unsure whether it went through submit again. The weak connection manufactures real duplicates.
- A Google Form has no camera scanner of its own, so the code usually has to be read by one app and pasted into another, which invites the double submit.
What QR to Sheets does with a repeat scan
This is the product we make, so treat it as one design rather than a claim about every tool. It separates the accidental repeat from the deliberate one. A code held in front of the camera is accepted once; it has to leave the frame before the scanner will take it again, so a phone resting over a badge does not write a row every second.
A code scanned again on purpose is always written. When the same value repeats on the same scanner link within 12 hours, the screen turns amber, says Already scanned with the number of times on this link, and adds Saved anyway. The scan is never blocked, and if the repeat check is unavailable for any reason the scanner simply shows no badge and carries on. The window is per link, so a code scanned on a Check out link and then on a Check in link does not warn.
Two quieter behaviours matter for duplicates too. A scan resent after a dropped connection is not written twice, so a flaky signal does not double your rows. And each scan is saved on the phone first, so the worker does not rescan out of doubt when the network is slow.
| Capability | Google Forms + formulas (free) | QR to Sheets |
|---|---|---|
| Cost | Free | Free for 300 scans, then 7 USD per registered device per month |
| Warning when a code repeats | None | Amber Already scanned badge with the count, and Saved anyway |
| Repeat window | Not applicable | 12 hours, counted per scanner link |
| Code left in front of the camera | Not applicable, no camera scanner | Accepted once; it must leave the frame first |
| Resent after a dropped connection | Submission lost, often retyped | Not written twice |
| Is the repeat row written | Yes | Yes, always |
| Deduplicated register | Formulas you write | Formulas you write |
Which scan should count?
- Attendance and sign-in: the first scan per person per day is the arrival. Keep later scans; they are the answer when someone asks what time a person left.
- Check in and check out: do not deduplicate across directions. Use two scanner links and compare the counts per code; a repeat within one direction is the thing to investigate.
- Stock counts: a repeat is often a real second unit. Never deduplicate a count blindly; count scans per code instead, and treat a sudden run of repeats as a phone held over one label.
- Event entry: a repeat usually means re-entry or a shared code. Flag it for the door rather than hiding it from the report.
Step by step
- 1
Decide what a repeat means in your workflow
For attendance, a second scan the same day is usually noise. For check-out, it may be a genuine second trip. For a stock count, it may be a second unit of the same product. Write the rule down before you write a formula.
- 2
Leave the raw log alone
Never delete rows from the tab your scans land in. Every report, including the deduplicated one, is a view built on top of it, so a wrong formula can always be fixed and a disputed record can always be checked.
- 3
Add a date column and a first-scan flag
A helper column holding the date of each scan, and a running COUNTIFS that marks the first occurrence of each code on each date, gives you a TRUE/FALSE you can filter, pivot and count on.
- 4
Build the clean tab from the flag
A FILTER or QUERY over the log that keeps only first scans is the deduplicated register. It updates itself as new rows arrive, so nobody has to clean the data by hand.
- 5
Tell the people scanning what a warning means
If your scanner shows a repeat warning, say in the same message as the link that the scan was still saved and that nobody needs to rescan. Most real-world duplicates come from people who were not sure the first scan worked.
Frequently asked questions
How do I remove duplicate QR scans in Google Sheets?+
Do not remove them from the log. Add a date column and a first-scan flag with COUNTIFS, then build a separate tab with FILTER or QUERY that keeps only first scans. The raw log stays complete and the clean tab updates itself as new scans arrive.
Does QR to Sheets block a duplicate scan?+
No. Every deliberate scan is saved. A repeat of the same value on the same link within 12 hours shows an amber badge with the repeat count and the words Saved anyway, so the person scanning knows without anything being refused. A code held in front of the camera is only accepted once until it leaves the frame.
Will scanning someone in and out trigger the repeat warning?+
Not if you use two scanner links, one for each direction, which is the recommended setup anyway. The repeat window is counted per link, so the same badge scanned on a Check in link and then a Check out link does not warn.
Can a Google Form stop someone submitting twice?+
Only by account. The Limit to 1 response setting requires respondents to sign in to their Google Account, so it limits each account to one response. It cannot recognise the same code submitted twice, and it adds a sign-in step that most scanning workflows cannot afford.
Why do I see the same code twice a few seconds apart?+
Usually because the person scanning was not sure the first scan worked, or the code was typed and pasted twice. Clear success feedback on the scanner and a note in the instructions that a scan does not need repeating remove most of them. Treat pairs seconds apart as one event in your formulas.
What formula keeps only the first scan per person per day?+
With timestamps in column A, codes in column B and the date of each scan in column G, put =COUNTIFS($B$2:B2, B2, $G$2:G2, G2)=1 in the first data row of a new column and fill it down. It is TRUE for the first scan of each code on each date. Filter on TRUE.