Detect Possible Duplicate Invoices

Duplicate payments are one of the most common and most avoidable losses in accounts payable. They rarely come from the same invoice typed twice character for character — the system would catch that. They come from the same invoice entered twice in slightly different ways: INV-001042 keyed by one clerk and 1042 by another, a scanned copy entered a week after the emailed PDF, a supplier set up twice as "Acme Fasteners Ltd" and "ACME Fasteners Limited". An exact-match check misses all of them. This page builds the checks that find them in an open-invoices export with pandas: invoice numbers normalised before comparison, same supplier and same amount within a window of days, supplier names normalised so that two records for one company compare equal — each match with a score and a plain-language reason, so the review list can be worked through before the next payment run. It belongs to Validating Excel Data with Python.

Prerequisites

pip install pandas openpyxl

The sample is a fourteen-line payables export with the usual mixture: a true exact duplicate, the same invoice keyed with and without its prefix and zeros, a separator typed differently, a supplier recorded under two names, two invoices with the same amount four days apart, and a monthly service charge that legitimately repeats.

Step 1 — Normalise the fields you compare

import re
import pandas as pd

inv = pd.read_excel("ap_invoices.xlsx", dtype={"Invoice": str})
inv["line"] = range(2, len(inv) + 2)                              # Excel row number for the reviewer

SUFFIXES = r"\b(ltd|limited|plc|inc|llc|gmbh|co|company)\b\.?"
def norm_supplier(name):
    s = re.sub(SUFFIXES, "", str(name).lower())
    return re.sub(r"[^a-z0-9]", "", s)

def norm_invoice(number):
    s = re.sub(r"[^A-Za-z0-9]", "", str(number)).upper()
    digits = re.sub(r"\D", "", s).lstrip("0")
    return digits or s

inv["supplier_key"] = inv["Supplier"].map(norm_supplier)
inv["invoice_key"] = inv["Invoice"].map(norm_invoice)
print(inv[["line", "Supplier", "supplier_key", "Invoice", "invoice_key"]].head(7).to_string(index=False))

Two normalisations do most of the work. Supplier names are lower-cased, stripped of legal suffixes such as Ltd and Limited and of everything that is not a letter or digit, so "Acme Fasteners Ltd" and "ACME Fasteners Limited" both become acmefasteners. Invoice numbers keep only their digits without leading zeros — INV-001042, 1042 and inv 1042 all become 1042, and BL/7781 and BL-7781 both 7781 — falling back to the letters when a number has no digits. The originals stay in the data; the keys exist only to compare. Normalising invoice numbers down to digits is deliberately aggressive — it would also match PO-1042 with INV-1042 from the same supplier — which is acceptable because a match on the number alone only produces a lower-scoring flag for a person to check, never an automatic decision. The line column records the Excel row — the DataFrame index plus two, for the header row and Excel's numbering from one — so the reviewer can go straight to it.

Step 2 — Find candidate pairs and score them

Compare every pair of invoices from the same normalised supplier — pairs, not rows, because a duplicate is always a relationship between two lines — and score how alike they are:

pairs = inv.merge(inv, on="supplier_key", suffixes=("_a", "_b"))
pairs = pairs[pairs["line_a"] < pairs["line_b"]].copy()
pairs["days_apart"] = (pairs["Date_b"] - pairs["Date_a"]).dt.days.abs()
pairs["same_amount"] = (pairs["Amount_a"] - pairs["Amount_b"]).abs() < 0.005
pairs["same_number"] = pairs["Invoice_a"] == pairs["Invoice_b"]
pairs["same_number_key"] = pairs["invoice_key_a"] == pairs["invoice_key_b"]
pairs["same_name"] = pairs["Supplier_a"] == pairs["Supplier_b"]

def reason(r):
    if r.same_number and r.same_amount and r.days_apart == 0:
        return 100, "exact duplicate"
    if r.same_number_key and r.same_amount:
        return 90, "same invoice, formatted differently" + ("" if r.same_name else " under another supplier record")
    if r.same_amount and r.days_apart <= 7:
        return 60, f"same amount {r.days_apart} days apart, different number"
    if r.same_number_key:
        return 50, "same invoice number, different amount"
    return 0, ""

pairs[["score", "reason"]] = pairs.apply(reason, axis=1, result_type="expand")
suspects = pairs[pairs["score"] > 0].sort_values("score", ascending=False)
print(suspects[["line_a", "line_b", "Supplier_a", "Invoice_a", "Invoice_b", "Amount_a", "score", "reason"]].to_string(index=False))

A self-merge on the supplier key produces every pair of invoices from the same company, and keeping only pairs where the first line comes before the second removes self-matches and mirror pairs. Each pair is then classified, most certain first: identical number, amount and date is an exact duplicate; the same normalised number and amount is the same invoice entered twice, and if the supplier names differ too, the supplier is probably set up twice; the same amount from the same supplier within seven days under a different number is the classic scanned-copy-plus-email duplicate, with the evidence given in days. The monthly Evergreen charge, a month apart, and the two Granite invoices with different amounts are not flagged. Four pairs come out: the Fjord IT invoice entered twice exactly, the Acme invoice entered once as INV-001042 and once as 1042 under a second supplier record, the Brightline invoice keyed with a slash and with a hyphen, and the two Corex invoices for the same amount four days apart — the one case a person has to decide by looking at the documents.

Step 3 — The review list

review = suspects.assign(Lines=suspects["line_a"].astype(str) + " & " + suspects["line_b"].astype(str))[
    ["score", "reason", "Lines", "Supplier_a", "Supplier_b", "Invoice_a", "Invoice_b", "Date_a", "Date_b", "Amount_a"]]
at_risk = suspects.loc[suspects["score"] >= 60, "Amount_b"].sum()
with pd.ExcelWriter("duplicate_review.xlsx", engine="openpyxl", date_format="dd mmm yyyy", datetime_format="dd mmm yyyy") as xw:
    review.to_excel(xw, sheet_name="Review", index=False)
    inv.drop(columns=["supplier_key", "invoice_key"]).to_excel(xw, sheet_name="Invoices", index=False)
print(f"{len(review)} pairs to review; value at risk (score >= 60): {at_risk:,.2f}")

The review sheet lists each suspect pair once, highest score first, with both Excel line numbers, both supplier names and invoice numbers, the dates, the amount and the reason in words. The value at risk — the second invoice of every pair scoring 60 or more — is the figure that justifies the check: here 20,010.50 in a fourteen-line file: the Fjord IT exact duplicate, the Acme invoice entered under two supplier records, the Brightline invoice keyed with two different separators and the Corex pair four days apart. Pairs at 100 or 90 should block payment of the second line until resolved; pairs at 60 need someone to look at the two documents. Running this before every payment run, rather than in an annual audit, is where it pays off; the payment run itself is covered in Build a Supplier Payment Run Proposal.

Tuning the rules on real data

The thresholds above — seven days, an exact amount match, the list of legal suffixes — are starting points. Run the check on a few months of past invoices where the outcome is known and adjust. If legitimate repeat charges keep appearing at score 60, such as weekly deliveries of a fixed amount, either narrow the window or exclude suppliers marked as recurring. If duplicates are being missed, look at how they differ: amounts that differ by the VAT element suggest comparing net and gross amounts both ways; numbers with transposed digits suggest a fuzzy comparison of the invoice key, for which a library such as RapidFuzz is a small addition. Keep every rule explainable in a sentence on the review sheet. A score nobody can explain is ignored; "same invoice number, formatted differently, under another supplier record" gets acted on, and often uncovers the duplicate supplier record that caused it.

Common pitfalls

• Exact matching only. Real duplicates differ in formatting; compare normalised keys.
• Comparing within one supplier name. Duplicate supplier records hide duplicates; normalise names.
• Listing rows instead of pairs. A reviewer needs both invoices side by side to decide.
• Flagging recurring charges. Use a day window and allow known repeating suppliers.
• Scores without reasons. Every flag needs a sentence a reviewer can check.

Conclusion

To catch duplicate invoices, normalise supplier names and invoice numbers, self-merge the invoices on the supplier key to get every pair from the same company, and score each pair from exact duplicate down to same amount within a few days, with a reason in words. Write the pairs to a review sheet with line numbers and the value at risk, block payment on the strongest matches, and tune the rules on past data so legitimate repeats stay off the list.

Frequently asked questions

How do I find duplicate invoices in Excel with Python? Normalise invoice numbers and supplier names, self-merge the invoice table on the supplier key to compare every pair, and flag pairs with the same normalised number and amount or the same amount within a few days.

How do I match invoice numbers like INV-001042 and 1042? Remove non-alphanumeric characters and keep the digits without leading zeros as a comparison key, while keeping the original number for display.

How do I find duplicates when the supplier is set up twice? Normalise supplier names by lower-casing them and removing legal suffixes such as Ltd and Limited and punctuation, and compare on that key.

How many days apart should possible duplicates be? Seven days is a reasonable starting window; adjust it using past invoices so recurring charges are not flagged.

Related

• Up one level: Validating Excel Data with Python — checks on Excel data.
• Find Duplicate Rows in Excel with Python — exact duplicates.
• Find Near-Duplicate Names Within One Excel Column — fuzzy name matching.
• Build a Supplier Payment Run Proposal — where the check belongs.