How to build a vendor invoice tracker that finds missing and duplicate payments

The decision hiding in the data

One invoice can be paid in installments, one payment can settle several invoices, and credit notes may arrive later. Matching only amount or invoice number can flag legitimate repeats or miss duplicates across vendor naming variations.

Choose the approach that matches the evidence and repetition

From the field · Operations coordinator

Reconcile supplier invoices before the weekly payment run

A supplier resubmits one invoice with a different filename, another invoice has two purchase orders, and one bank payment covers three approved invoices after a credit note.

Columns used in the example

vendor_id · invoice_number · invoice_date · invoice_amount · po_number · credit_number · payment_id · payment_amount

Follow it with your own file

1. make consistent vendor IDs and invoice references without deleting raw strings.
2. Create duplicate candidates within vendor using reference, amount, and date evidence.
3. Allocate payments and credits explicitly across invoice balances.
4. Publish approved-to-pay, paid, blocked, and unresolved exception groups.

Check: Invoice amount minus credits minus allocated payments must equal open balance for every invoice; allocation totals must equal payment and credit records; no document may disappear.

Before touching the file

• Use governed vendor IDs and reviewed aliases, retaining source names.
• Keep purchase orders, invoice lines, credits, payments, and allocations separately.
• Lock exact references before controlled amount/date and grouped candidates.
• Track duplicate, missing PO, amount, tax, timing, credit, and unresolved balances.

An invoice status is the result of a ledger

A single Paid flag cannot explain partial settlement, overpayment, credit application, or grouped remittance. Keeping document and allocation grains preserves the evidence required for approval and duplicate prevention.

Where this usually goes wrong

Never approve a changed bank account or urgent payment using spreadsheet data alone. Follow independent vendor-verification and payment-control procedures.

Build the result step by step

1. Resolve vendor identity: Use governed vendor IDs and reviewed aliases, retaining source names.
2. Model document relationships: Keep purchase orders, invoice lines, credits, payments, and allocations separately.
3. Match in tiers: Lock exact references before controlled amount/date and grouped candidates.
4. Manage exceptions: Track duplicate, missing PO, amount, tax, timing, credit, and unresolved balances.

Where to look for exceptions

• The same invoice number appears under two vendor spellings.
• Paid status is true even though payment amount differs from invoice balance.
• Credits and partial payments are overwritten rather than recorded.

Before the result leaves your desk

• Vendor IDs reviewed
• Documents kept at correct grain
• Duplicate candidates resolved
• Allocations balance
• Payment controls followed

Doing these steps in Prepyr

Prepyr accepts CSV files only. Export the worksheet or table you need as a UTF-8 CSV first; workbook formulas, formatting, macros, charts, and other sheets will not be included. Describe the result you need: Build a vendor invoice tracker that detects duplicate and unmatched payments. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.

Prepyr steps used here: Data cleaning · Verified analysis · Reusable workflows · Reports

Questions readers usually ask next

Is the same invoice number always a duplicate?

Not across different vendors. Within a vendor it is a strong candidate, but revisions, credits, and source behavior still require review.

How should one payment covering several invoices be handled?

Create explicit allocation records that sum to the payment and reduce each invoice balance separately.