Duplicate tuition payments in a spreadsheet: flag, verify, then correct
A repeated tuition-payment row is not necessarily a second payment, and two payments for the same amount are not necessarily duplicates. Flag candidates using transaction evidence, compare them with the original receipt source, and exclude only confirmed duplicate imports. Preserve the raw export and the reason for every correction.
Why duplicate tuition payments appear in spreadsheets
Overlapping exports are one common mechanism: a 1–15 Nov file and a 10–30 Nov file both include the same receipt from 12 Nov. Copying both into one payment tab creates two rows for one event.
Other repeated-looking rows have different explanations:
| What you see | What to verify |
|---|---|
| Same transaction ID in two imported files | Whether one receipt was imported twice |
| Same date and amount, different transaction IDs | Whether the family made two genuine transfers |
| One receipt and one processor settlement | Whether you combined payment and bank-payout sources |
| A successful receipt and a reversal | Whether the second row cancels the first rather than repeating it |
| Blank references with matching amounts | Whether stronger evidence identifies the events |
Choose a source for family receipts. A processor payout to your bank may combine several receipts and fees; it is not automatically another family payment.
Preserve source columns before flagging rows
In your working payment table, keep payment ID, family ID, received date, amount, currency, source file and source row. Use a processor or bank transaction ID where one is available. An invoice number alone is not necessarily a unique payment ID: one invoice can receive installments.
Don’t let Excel strip leading zeros from references. Store IDs as text. Keep the untouched exports so you can compare a candidate with the original record.
Add a review flag, not an automatic delete
If column A contains nonblank transaction IDs from one payment source, this Excel or Google Sheets formula flags repeated IDs:
=IF(A2="","Review: missing ID",
IF(COUNTIF($A$2:$A$1000,A2)>1,
"Review: repeated ID","ID not repeated"))
Extend the range for your data. COUNTIF treats * and ? as wildcards, so use this simple version only for IDs without those characters. If you combine sources, use a source-qualified key so an ID from one system isn’t mistaken for the same ID in another.
“ID not repeated” means exactly that. It doesn’t validate the family assignment, amount or receipt status.
Without stable IDs, flag matching combinations of date, amount, currency and reference for review. Don’t invent certainty from a weak key. Two same-day USD 40.00 cash receipts might both be real.
Work through an overlapping-export example
The following records are fictional. FAM-404 owes USD 160.00 for November and has no opening balance or credit.
| Source | Transaction ID | Receipt date | Amount |
|---|---|---|---|
| Export A, row 8 | TX-404 | 12 Nov 2026 | USD 80.00 |
| Export B, row 3 | TX-404 | 12 Nov 2026 | USD 80.00 |
The working sheet initially totals USD 160.00 paid and shows USD 0.00 owed. The original payment source confirms only one TX-404 receipt. Excluding the duplicate import leaves USD 80.00 paid and USD 80.00 owed.
Keep one included row and mark the other “Excluded: same TX-404 receipt also present in Export A, row 8,” with the review date. You are correcting the imported ledger, not reversing a payment at the bank.
If the source instead confirms TX-404 and TX-405 as two separate USD 80.00 transfers, both belong in the ledger. Their equal amounts are not a reason to remove either.
Check totals after the correction
- Compare included receipts with the chosen payment source for the same period.
- Total excluded rows separately and retain their reasons.
- Recalculate affected family balances.
- Verify that no receipt is counted both in the opening balance and in the current period.
- Review any reversal or refund as its own event rather than hiding it as a duplicate.
A reconciled school total is not enough if you removed one family’s legitimate payment while retaining another family’s duplicate. Recheck the affected IDs and receipt evidence.
If a statement has already gone out, preserve it and issue a clearly identified correction. Explain the specific receipt change without implying the family caused an import error.
The opening-balance rollover guide covers receipts that are genuine but already included in an earlier period. Tufton’s public sample shows the source-linked movement structure to look for when reviewing a family ledger.