Find duplicate transactions in Google Sheets

Find duplicate transactions in Google Sheets by counting repeated transaction keys within the correct entity and currency. Use the result to create a review queue, then reconcile any approved exclusions to the original export. A repeated amount is a reason to investigate, not permission to change a ledger.

Define what one transaction means

Start with an unchanged ledger or payables export. Preserve source row number, entity, transaction identifier, posting date, vendor identifier, signed amount, and currency. Agree whether each row represents a whole transaction or a transaction line before building the key.

For whole-transaction rows, entity plus source transaction identifier may identify the record. This example also includes currency as a scope field. For line-level exports, include a stable line identifier; otherwise legitimate lines belonging to the same transaction will all be flagged.

If the same identifier is reused across source systems or fiscal years, add those fields too. An incomplete key can make unrelated transactions look identical. The broader business spreadsheet data quality guide covers completeness and control totals before analysis.

Set up a small review sheet

A US distributor has these whole-transaction rows. Positive values represent charges in this analytical export; negative values are credits. Columns A:F contain the fields in the order shown.

The source total is 1,250 + 1,250 + 800 - 800 + 600 + 1,250 = $4,350. These are exact dollar amounts, with no rounding. The company confirms that row 5 reverses row 4 and that US02 is a separate legal entity.

Do not combine entities just because the vendor and transaction labels match. If you use Excel for the same review, the neighboring Excel duplicate-transaction guide covers that workbook workflow.

Count repeated keys with COUNTIFS

In G1 enter Key. In G2, build a visible review label and fill down:

=A2&"|"&F2&"|"&B2

This display assumes the delimiter does not occur in the source identifiers. The count below compares the fields separately, so it does not depend on parsing the joined label.

In H1 enter Key count. In H2, enter and fill down:

=IF(OR(A2="",B2="",F2=""),"MISSING KEY",COUNTIFS($A$2:$A$7,A2,$B$2:$B$7,B2,$F$2:$F$7,F2))

Google's COUNTIFS documentation describes counting rows that meet multiple criteria and requires corresponding criteria ranges to have the same dimensions. Keep the anchored ranges aligned as the export grows.

The formula intentionally excludes amount from the key. If the same transaction identifier appears twice with different amounts, that discrepancy still needs review. Adding amount to the key would separate those records and hide the conflict.

The expected result is:

Filter H for values above one, and separately review MISSING KEY rows. If you want visual highlighting, apply conditional formatting to A2:I7 with the custom formula =AND(ISNUMBER($H2),$H2>1). Google documents custom formula formatting and duplicate highlighting.

Review the documents before excluding a row

Compare rows 2 and 3 with the source transaction and import record. In this example, the reviewer confirms that row 3 is a repeated export record, while row 2 is the retained original. This is an analytical exclusion; it does not establish that the accounting system contains a duplicate posting.

In I1 enter Decision. Mark row 3 Exclude approved and all other rows Keep. Preserve the reviewer, date, reason, and supporting reference alongside the review in your working process. Unresolved rows should remain explicitly unresolved rather than silently disappearing from a summary.

Rows 2 and 3 together represent $2,500 of flagged activity. The approved excess is only $1,250. Reporting the entire flagged amount as an overstatement would count the legitimate original too.

Reconcile the approved analytical view

Calculate the retained and excluded totals from the decision column:

=SUMIF(I2:I7,"Keep",E2:E7)
=SUMIF(I2:I7,"Exclude approved",E2:E7)
=SUM(E2:E7)-SUMIF(I2:I7,"Keep",E2:E7)-SUMIF(I2:I7,"Exclude approved",E2:E7)

Google's SUMIF documentation describes summing values that match a criterion. Here the retained amount is 1,250 + 800 - 800 + 600 + 1,250 = $3,100. The approved exclusion is $1,250, and the reconciliation is 4,350 - 3,100 - 1,250 = zero.

Require every row to have a reviewed decision as well: a zero-dollar unresolved record would not disturb that amount check. Feed the approved analytical total into a financial dashboard in Google Sheets, while keeping its source and exclusion record available. Investigate unexpected month-to-month changes with flux analysis.

Where it goes wrong

• Matching amounts alone. Recurring charges and different entities can produce identical amounts.
• Ignoring line-level detail. A transaction can legitimately occupy several ledger rows.
• Removing reversals. Preserving signed entries keeps the accounting relationship visible.
• Treating flags as findings. Only source review establishes the reason for repetition.

Covirage is not affiliated with Google or Microsoft.

Analyze the reviewed export in Covirage

Bring a sample of your own ledger export or Google Sheet with the identifiers, signed amounts, and reviewed decisions. Covirage maps the columns, its tools compute and check the figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring your file.

See Covirage for finance teams for the reporting context.