Dynamic Arrays
FILTER to show duplicate rows: inspect all collisions vs 2nd+ repeats
Extract every transaction row involved in a duplicate key collision (TotalCount > 1) so AP auditors can compare both copies side by side.
All rows in a collision (TotalCount > 1) vs extra copies only (RunCount > 1)
Use a full-range count to show every row sharing a key, and a running count to identify later occurrences.
Repeated keys are audit candidates. They do not by themselves establish duplicate billing.
| InvNum (A) | Amount (B) | TotalCount (C) | RunCount (D) | Audit Role |
|---|---|---|---|---|
| INV-901 | $500 | 2 (Collision) | 1 (Original) | Shown in Collision View |
| INV-902 | $320 | 1 (Unique) | 1 | Excluded |
| INV-901 | $500 | 2 (Collision) | 2 (Excess Copy!) | Repeated-key $500! |
| INV-903 | $180 | 1 (Unique) | 1 | Excluded |
| INV-904 | $250 | 2 (Collision) | 1 (Original) | Shown in Collision View |
| INV-904 | $250 | 2 (Collision) | 2 (Excess Copy!) | Repeated-key $250! |
Repeated-key excess-duplicate sum and collision-extraction formulas
The full-row report below shows all candidates; the companion sum totals later-occurrence amounts for investigation.
=FILTER(A2:B7,C2:C7>1,"None")Count total rows involved in any duplicate collision
Counts both original and repeat rows where TotalCount > 1 (4 rows across 2 collided invoices).
=COUNTIF(C2:C7,">1")Additional worked calculation
Using the initial example inputs:
=SUMIF(D2:D7, ">1", B2:B7)Additional worked calculation
Using the initial example inputs:
=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(A2:A7, C2:C7 > 1)))Inputs and interpretation
These are repeated-key candidates for investigation, not proven duplicate invoices or overcharges. Define the business key, potentially vendor plus invoice plus line, before interpreting amounts. The first occurrence means current row order, not necessarily the original transaction. Compare case and whitespace policies deliberately; do not delete rows solely from this report.
Try the example
Use the original values or change them to test your understanding.