Detect Duplicates and Anomalies in Payables Data

Workflow area: Finance & Accounting · Tier: Advanced · Hands-on time: ~45 minutes

The AI's role here is precise: it is very good at generating screening logic and summarizing patterns, and untrustworthy at the arithmetic itself unless it uses real computation. The design below keeps detection rules explainable, results reconciled, and every judgment — "is this actually a duplicate?" — with a human who can see the underlying documents.

When to use — and when not to

• Use it for periodic hygiene sweepsmonthly or quarterly passes over AP transactions, expense claims, or vendor master data, especially before an audit.
• Use it to build rules you keepthe output worth having is a reusable screen you rerun, not a one-off finding.
• Skip it as a fraud investigation toolif you already suspect fraud, stop and escalate to your controller or auditors; an amateur AI-assisted sweep can contaminate an investigation and tip off a subject.
• Skip auto-remediation entirelynothing in this workflow blocks a payment, reverses a transaction or emails a vendor. It produces a reviewed exceptions log for humans to act on. Ever.

The recipe

1. 1.

   Extract and minimize the data. Export the period's AP transactions: invoice number, vendor ID (coded, not named if policy requires), invoice date, entry date, amount, currency, payment status, entered-by role (role, not person's name), approval threshold band, and GL code. Strip bank account numbers, vendor contact details, and employee names — the screen needs patterns, not identities.

2. 2.

   Have AI design the rule set before touching the data.

   Copy

   I am screening accounts-payable transactions for duplicates and
   anomalies. Columns available: [PASTE COLUMN LIST].
   
   Propose a screening rule set:
   - Exact duplicates: same vendor + amount + invoice number.
   - Near-duplicates: same vendor + amount within 1% + dates within
     10 days, OR invoice numbers differing by one character.
   - Threshold games: amounts within 5% under an approval threshold,
     and vendors with multiple same-day invoices that individually
     sit under a threshold.
   - Timing: entries on weekends/holidays, invoice dates after entry
     dates, round-amount clusters.
   - Vendor oddities: new vendors with immediate large invoices,
     one GL code receiving many different vendors' round amounts.
   
   For each rule: exact logic, why it matters, and its likely
   false-positive sources. Format as a table I can keep.
   

   Review the rules with your finance lead — some will not fit your process (weekend entries are normal if AP works Saturdays in month-end close). Tuning rules to your reality is what keeps the flag list reviewable instead of overwhelming.

3. 3.

   Run the screen with real computation. Upload the CSV to ChatGPT data analysis (or Claude with analysis enabled) and run the tuned rules:

   Copy

   Apply the agreed rule set to this CSV using actual computation —
   write and execute code, do not estimate.
   
   Output:
   1. Summary: rows screened, flags per rule, total unique flagged
      transactions.
   2. Flag detail table: transaction row IDs, rule triggered,
      matched counterpart (for duplicate rules), and the values
      that triggered the flag.
   3. Reconciliation line: total amount of all transactions vs. sum
      of flagged + unflagged — these must match exactly.
   

   The reconciliation line is non-negotiable: if flagged + unflagged does not equal the file total, the run is invalid. Independently verify one rule yourself in Excel (COUNTIFS for exact duplicates takes two minutes) and confirm the counts agree.

4. 4.

   Triage every flag into an exceptions log. Build a sheet: Flag ID | Rule | Transactions | Reviewer | Finding (duplicate / legitimate / needs documents / escalate) | Evidence checked | Action | Date. A human opens the underlying invoices for each flag. Most flags will be legitimate — recurring rents trip near-duplicate rules; installment payments look like split invoices. That is fine: the log records why each flag was cleared, which is exactly what an auditor wants to see.

   Copy

   Sample log entry:
   F-014 | Near-dup | rows 812, 1147 | R. Iyer | Legitimate —
   monthly retainer, same amount by design | Contract checked |
   No action | 12-Aug
   

5. 5.

   Summarize and systematize. Ask the AI to draft a one-page summary from your completed log: flags raised, cleared, confirmed exceptions, amounts recovered, and rule false-positive rates. Then feed the false-positive rates back into the rules (raise a tolerance, add an exclusion for known recurring vendors) and save the tuned rule set — next month's run starts at step 3.

Make it yours

• Excel-native version. If uploads are restricted, have the AI write the formulas instead: ask for COUNTIFS/SUMPRODUCT duplicate checks and conditional-formatting rules matching each screen, paste them into Excel, and keep everything inside your environment.
• Expense-claims variant. Same rules, different columns: claimant role, category, receipt date vs claim date, per-diem thresholds. Just-under-limit clustering is the classic finding.
• Vendor-master sweep. Run a companion screen on the vendor file itself — duplicate bank accounts across vendor IDs (hash them first), similar vendor names, dormant vendors suddenly active. Master-data duplicates cause payment duplicates.

Quality check

• The reconciliation line balancesflagged + unflagged equals the file total, to the paisa.
• One rule was verified independently in Excelthe model's counts matched yours.
• Every flag has a named human reviewer and a recorded findingno flag closed by AI.
• False positives were logged, not deletedcleared flags with reasons are audit evidence.
• The rule set is saved and versionedthis month's tuning makes next month's run better.