Health and family

Catch duplicate and missing-receipt expenses early: Canadian crew

For a small Canadian contracting crew, flag out-of-policy expenses before month end by catching duplicate charges and missing receipts with simple formulas.

Editorial illustration for Catch duplicate and missing-receipt expenses early: Canadian crew

A small contracting crew with fuel cards and a supply-store account can flag out-of-policy expenses before month end by catching two things automatically: possible duplicate charges and missing receipts. Both can be done with one formula each. The amounts below are in Canadian dollars and invented. The rest is a weekly ten-minute check, not a monthly surprise.

What goes wrong on a crew

Three people use the same card at the same supply store in one morning. One buys screws for C$38.40, another is sent back for the same screws and buys them again. A receipt blows away in a truck. Neither is dishonest, and both cost money that is hard to explain at the end of the month.

Set up the log

  • A: date. B: who bought. C: supplier. D: amount. E: job name. F: receipt photo Yes or No.

Formula one: possible duplicates

In column G, enter =IF(COUNTIFS($A$2:$A$500,A2,$C$2:$C$500,C2,$D$2:$D$500,D2)>1,"Possible duplicate",""). It counts rows with the same date, supplier and amount, and flags a row if more than one exists. It works in Excel and Google Sheets. It will sometimes flag two legitimate purchases of the same item, which is why it says possible.

Formula two: missing receipts

In column H, enter =IF(F2<>"Yes","Receipt missing",""). Then at the top of the sheet, =COUNTIF(H2:H500,"Receipt missing") shows how many are outstanding. Treat anything missing after three working days as a conversation, not a mystery.

An example morning

WhoSupplierAmountFlag
SamSupply storeC$38.40Possible duplicate
PriyaSupply storeC$38.40Possible duplicate
SamFuelC$91.15Receipt missing
PriyaLumber yardC$212.60None
TotalAll rowsC$380.553 flags

Check: 38.40 plus 38.40 is 76.80, plus 91.15 is 167.95, plus 212.60 gives C$380.55. A phone call settles the first two: they bought the same screws, and one set goes back for a refund. Without the formula, nobody would look.

How do we handle the refund?

Enter it as a negative line on the day it happens, with the same supplier and a note: returned screws. Your total then reflects real spending, and the duplicate no longer stands out. A refund that arrives as a credit on the next statement can be matched to that line.

What if a flagged charge is wrong, not just odd?

Sometimes a duplicate is a supplier error. Keep the statement line and the receipt, contact the supplier with the date, amount and job, and write the request date in a notes column. When the credit arrives, enter it as a negative line and mark the flag as resolved. Do not delete the original charge, since it is the evidence that a mistake happened and was fixed.

It is worth checking the card statement itself, not just your sheet, at least once a month. The sheet shows what the crew recorded; the statement shows what was actually charged. Any statement line with no matching row in the sheet is a purchase nobody logged, and that is a different problem from a duplicate.

A ten-minute Friday check

  1. Sort by the flag columns.
  2. Ask for missing receipts.
  3. Review each possible duplicate.
  4. Enter refunds.
  5. Reset the counts for next week.

SpendixAI has a report that lists same-amount, same-merchant charges within two days for imported statements, but it is built for India and INR and is not a crew or approvals tool, so this sheet is the fit for a Canadian crew.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports