Most pay-application errors announce themselves. A formula breaks, a column shifts, a total doesn't tie out — something looks wrong, so somebody looks at it. A duplicate time-and-materials (T&M) ticket does the opposite. It sits inside a block of pasted ticket data that looks perfectly healthy, and it survives every review that reads tickets without counting them.
Here's the shape of the failure — no names, no dollar figures, because it doesn't need any. The same T&M ticket gets pasted into two billing cycles. On the subcontractor's side, nothing looks off: the paste goes in, the backup gets attached, the pay application goes out. Then the general contractor's reviewer runs a pass over the tickets, finds the same ticket number in two places, and flags it. The pay app stalls while the backup gets dug up and re-proved — for work that was already billed once.
The painful part is rarely the duplicate itself. It's the stall.
Why paste-based billing produces duplicates
If your billing runs on pasted data, duplicates are a structural risk, not a carelessness problem.
The billing-cycle boundary is the classic spot. Last cycle's tickets are still sitting in the file or the export when this cycle's batch gets pasted in, and the two ranges overlap. Nobody types the ticket twice in one sitting — it crosses a boundary, so each paste looks complete on its own.
Ticket data also arrives from more than one place. Field emails, PDF exports, a superintendent's spreadsheet: the same ticket can show up in two batches, and neither batch looks partial.
And eyes don't catch it, because duplicate rows don't look wrong. The columns line up. The labor and equipment entries are real. Inside a block of valid-looking rows, a duplicate reads as more work, not as an error.
A person reviewing tickets reads their content. A duplicate check reads their IDs. That's the whole difference, and it's why counting catches what reading doesn't.
What a COUNTIF check actually sees
EX-01 is the duplicate check in the Construction Control workbook. It counts duplicate ticket IDs with a COUNTIF on the ticket ID column of the pasted T&M ticket data, and the mechanics are simple enough to explain in full.
COUNTIF takes a range and a criterion, and returns how many cells in the range match it. For a five-row example, the formula next to the first ticket ID looks like this:
=COUNTIF($A$2:$A$6, A2)
Copied down the column, every row asks the same question: how many times does this exact ID appear in the column? In a real paste block, the range simply runs to the last row of data.
Here's the worked example, using invented ticket numbers for illustration:
| Ticket ID | COUNTIF result |
|---|---|
| TM-1041 | 1 |
| TM-1042 | 2 |
| TM-1043 | 1 |
| TM-1042 | 2 |
| TM-1044 | 1 |
Rows two and four hold the same ticket, pasted into two cycles — the failure described above. Every ID counts 1 except TM-1042, which counts 2 in both places. That's the entire mechanism.
Notice what the check is doing. It doesn't understand tickets, labor, billing cycles, or backup. It compares values in one column and counts them. That makes it exhaustive on exactly one question — does this exact ticket ID appear more than once? — and blind to everything else, including one case you should know about before you rely on it.
The limitation, stated plainly
EX-01 catches the same ticket number entered twice. It does not catch a ticket that was renumbered.
If a ticket goes in once as TM-1042 and the same work goes in again under a reissued number — TM-1099, say — the column holds two different values. COUNTIF counts each of them once. Nothing flags, because nothing matches.
No formula reading the ticket ID column alone can catch that case; to an ID comparison, a renumbered duplicate looks like two legitimate tickets. That's why the plain description matters more than a flattering one. A check you understand is a check you can lean on for the errors it actually sees — and supplement for the ones it doesn't. You're not handing your judgment to a formula; you're using it for the one mechanical question it answers completely.
How the check fits the seven
EX-01 is one of seven checks in the workbook. The other six look at missing fields, unlinked tickets, change orders without backup, a totals comparison, the approved-but-not-billed gap, and billed ≠ approved.
The duplicate count is the most mechanical question in the set — one column, one count — and the one a reading reviewer is least equipped to answer by eye. Seven small questions, each aimed at one specific way paste-based billing data goes wrong.
Where to go from here
If you want to get the underlying process in order first, start with the free change-order log template guide:
https://ty-square-csv-preflt-2026-10.pages.dev/articles/change-order-log-template
A free checklist PDF is linked from the guide.
If you want the checks themselves, the Construction Control workbook is $39 — a macro-free Excel file with 528 formulas, including EX-01 and the six other checks named above.
Disclosure: this article was drafted with AI assistance.
Top comments (0)