AP controls · Guide
How to find duplicate invoices in Excel
Duplicates rarely arrive as two identical rows. They arrive as the same invoice with a different reference, a slightly different vendor name, or a supplier who re-sent under a new number. Exact matching finds the easy third.
The good news: you're not trying to automate a decision, you're trying to shrink a stack of 4,000 rows down to the twelve that a person should look at. Everything below serves that.
1. Normalise before you compare anything
Anything you compare text on needs a normalising pass first, in a helper column. This single formula kills most false negatives:
=TRIM(CLEAN(LOWER(SUBSTITUTE(SUBSTITUTE(A2,".",""),",",""))))It strips the invisible non-printing characters that come out of a PDF import, the trailing spaces that came out of a CSV, and the punctuation differences between Ltd. and Ltd. Do the same for invoice numbers, then strip leading zeros with TEXT(...,"0") only if your suppliers are inconsistent about them.
2. Exact duplicates: build a composite key
One column holding the things that must all match:
Key =Vendor_Clean & "|" & InvNo_Clean & "|" & TEXT(Amount,"0.00")
Flag =IF(COUNTIF($H$2:$H$5000, H2) > 1, "Review", "")Do not match on amount alone — recurring rent and subscription invoices will bury you in noise. Vendor plus amount plus a tight date window is usually the right balance.
3. Near-duplicates: the same payment, twice
This is the case that exact matching misses entirely and that costs the most money: the same invoice entered twice with different reference numbers. Catch it by matching vendor and amount within a window of days:
=COUNTIFS($A$2:$A$5000, A2,
$E$2:$E$5000, E2,
$B$2:$B$5000, ">="&B2-10,
$B$2:$B$5000, "<="&B2+10)Anything returning more than 1 is worth a look. Ten days is a reasonable default; tighten it to 3 if your volume is high and you're drowning in results.
4. Fuzzy matching vendor names
When names are genuinely different — Northwind Fabrication and Northwind Fabrications Ltd — Power Query does this better than worksheet formulas:
- Load the ledger twice as two queries.
- Merge Queries → join on the vendor name column.
- Tick Use fuzzy matching, set similarity threshold around 0.80, and enable Match by combining text parts if names are word-order inconsistent.
- Expand the matched columns and filter where the amount also agrees.
Fuzzy matching produces false positives by design. That's fine — you're triaging, not auto-rejecting. Set the threshold high enough that the list stays reviewable.
Never auto-reject on a fuzzy match. A legitimate split invoice — same vendor, same total, two deliveries — looks exactly like a duplicate. Every flag needs a human decision, and the decision needs to be recorded.
5. Make the output useful
A list of flagged row numbers is not a result. Include, on every exception row: the vendor, both document numbers, both amounts, the number of days between them, the payment status, and a blank decision and decided by column. That last pair is what turns this from a spreadsheet exercise into a control an auditor will accept.
6. Run it before the payment run, not after
Timing is the whole point. Run the check on the approved-invoice list immediately before payment selection. Run against paid history and you're writing a recovery letter; run against the pending list and you're deleting a row.
What probably goes wrong
- Amounts stored as text.
3200and"3200 "are different to Excel. Fix the type before comparing. - Credits and re-issues. A credit note followed by a corrected invoice is two legitimate rows. Exclude credit notes from the duplicate run.
- Volatile results. Anything using
TODAY()or an unsorted volatile range will change between runs and destroy your audit trail. Fix the run date in a cell. - No record of what you checked. Save each run's exception list. Next month, the same supplier will do it again.