Detect duplicate invoices without deleting legitimate line items

A repeated invoice number is not automatically a duplicate transaction. In a line-level export, one invoice normally appears on several rows because it contains several items. Detect duplicates at the intended business grain before removing anything.

Use a line identifier when the source guarantees one row per invoice line. Use an invoice identifier only after preparing an invoice-grain table. If invoice numbers repeat across suppliers or legal entities, include those fields in the business key as well.

Inspect two different repetitions

The synthetic spreadsheet quality workbook includes invoice I001 with two legitimate lines. L01 contributes 19,000 paise and L02 contributes 5,000, for an invoice total of 24,000.

It also includes L03 twice in RawSales. Those two rows have identical business fields and represent a repeated delivery under this fixture's explicit source contract. Each contributes 10,000 paise if counted, so retaining both overstates the clean amount by 10,000.

The repeated I001 invoice number is valid. The repeated L03 line is the defect. A generic duplicate highlight on InvoiceID cannot make that distinction.

Flag candidates at both grains

Inside RawSales, add a line-frequency helper:

=COUNTIF(RawSales[LineID],[@LineID])

The two L03 rows should each show two. All other line IDs should show one. A separate invoice-frequency helper shows how many exported rows belong to each invoice:

=COUNTIF(RawSales[InvoiceID],[@InvoiceID])

I001 shows two because of legitimate lines; I002 shows two because its line delivery is repeated. These counts are screening signals, not deletion instructions. The fixture's simple IDs contain no COUNTIF wildcard characters.

Structured table references keep the intended columns visible in the formula. Microsoft explains their behaviour in its structured-reference documentation.

Compare the complete business payload

For every repeated line key, compare invoice, customer, date, category, quantity, unit price, discount and status. Identical payloads may be replay candidates. Differing payloads might be corrections, versioned updates or conflicting records.

Do not select a winner based only on worksheet order. If the source supports revisions, use its authoritative revision contract. If the export lacks enough evidence, retain the exception and request source clarification through the normal business process.

For this fixture, the identical L03 replay can be excluded from the clean table while preserving both raw rows. Record the excluded source row and reason so the transformation remains reproducible.

Reconcile the removal

RawSales has eight rows and 82,500 paise. The clean Sales table has seven rows and 72,500. The one-row and 10,000-paise difference matches the approved replay exclusion.

January paid activity then contains four lines across three invoices, totaling 47,500. If you instead deduplicated on InvoiceID, one legitimate I001 line would disappear and the total would be wrong even if every remaining invoice number appeared unique.

The lab's reference checks independently verify these controls. Formula execution and any interactive removal operation require Excel review; the supplied clean output documents the intended result without instructing you to overwrite the raw sheet.

Build an invoice summary after cleaning lines

Once line-grain quality is established, aggregate by the full invoice business key. Sum line value and retain line count. Check that fields expected to be constant within an invoice, such as customer or currency, are consistent before selecting one representative value.

If an invoice has mixed statuses or dates, define whether those are line-level facts or source errors. Taking the first row can hide a meaningful discrepancy. An invoice summary should preserve exceptions that prevent a single authoritative invoice attribute.

Test a conflicting duplicate

Change one L03 copy's discount in a working copy. The duplicate-key check still identifies the same key, but identical-replay logic must now fail. Removing one row without explanation would discard evidence of a possible correction or error.

Exercise: add a third legitimate line to I001 with a new LineID. Verify that line-key checks accept it and invoice totals increase correctly. Then add a second supplier that also uses invoice I001 and explain why the invoice key must expand.

NeuraPath's Data Analytics with Generative AI course develops spreadsheet skills grounded in business grain and reconciliation. A credible duplicate-handling project explains what is repeated, why it is a defect and how the corrected totals were verified.

Continue learning

This article is part of the Excel and spreadsheet quality sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Excel missing values: distinguish blank cells and empty strings.
• Continue with Excel weighted averages for price and discount analysis.