Duplicate disbursements erode 0.8–1.1% of organizational spending annually (ACFE, Report to the Nations, 2024 ed., p. 47). Excel’s COUNTIFS function detects these control failures through systematic pairwise comparison: records matching on vendor and invoice identifiers but diverging on amounts, dates, or approval chains violate fundamental payment controls specified in PCAOB AS 2110.59.
The auditor implementing same-same-different analytics navigates calibration complexity. Raw mechanical matches generate 2–5% flag rates; without entity-specific filtering, false positives overwhelm investigative capacity. This article demonstrates three high-specificity patterns, integrates Claude prompting at workflow bottlenecks, and provides a companion workbook with reproducible synthetic data generation.
Before any flag is trusted, the practitioner does one unglamorous but decisive cleanup pass: normalize vendor names, normalize invoice formatting, and isolate legitimate credit-memo behavior. If that step is skipped, the workbook will flag formatting noise instead of actual control failures.
First-pass data hygiene before any flags
The minimum hygiene pass is short:
- normalize vendor names to one case-and-spacing convention,
- strip invoice prefixes, suffixes, and stray punctuation,
- push text-formatted amounts into numeric form, and
- identify documented credit memos before the duplicate test starts.
For a flat-file extract, the first formulas can be copied directly:
' Vendor normalization
=LOWER(TRIM(CLEAN(A2)))
' Invoice normalization
=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(CLEAN(B2),"-","")," ","")))
' Safe numeric amount
=IFERROR(VALUE(C2), NA())
This prevents the field failures that consume the most time in live files: ABC SUPPLY LLC versus ABC Supply LLC, INV-10492 versus 10492, and text amounts that visually match but do not compare as numbers.
Vendor-Invoice Duplication with Amount Divergence
Each vendor-issued invoice carries a single legitimate amount. Multiple database records sharing vendor and invoice identifiers but displaying different amounts signal either operational variations (credit adjustments, currency conversions) or control failures (duplicate payments, fraudulent resubmissions).
Core Detection Mechanics
Given vendor names in column A, invoice numbers in column B, amounts in column C, across rows 2 through 5001:
' D2: Total records matching this vendor-invoice pair
=IFERROR(COUNTIFS($A$2:$A$5001,A2,$B$2:$B$5001,B2), 0)
' E2: Records matching vendor-invoice with DIFFERENT amounts
=IFERROR(COUNTIFS($A$2:$A$5001,A2,$B$2:$B$5001,B2,$C$2:$C$5001,"<>"&C2), 0)
' F2: Flag when duplicates exist with amount variation
=AND(D2>1,E2>0)
The practitioner filters column F for TRUE values, generating the raw candidate population before calibration. The IFERROR wrapping ensures production resilience when encountering data type mismatches or reference errors.
Entity-Calibrated Filtering Through Claude Pre-Test Prompts
Generic thresholds fail to accommodate entity-specific business processes. The auditor extracts calibration parameters directly from policy documentation via Claude:
Analyzing accounts payable policy excerpt: “{paste credit memo procedures, split-invoice windows, currency handling sections}”. Generate Excel formulas for:
1. Sign-consistency filter accommodating documented credit memo processing
2. Time-window filter based on stated split-invoice approval periods
3. Currency stratification per multi-currency vendor procedures
Assume: vendor in A, invoice in B, amount in C, posting date in G, currency in H.
Include rationale linking each threshold to specific policy language.
For an entity documenting 30-day split-invoice windows and EUR/USD operations, Claude generates:
' Sign consistency check using structured table references.
' The LET binds three named intermediates:
' matches = row numbers of every OTHER row with the same vendor+invoice as the current row
' paired_amount = the dollar amount on the first such matching row (defaults to the current
' row's amount when there is no match, so the final SIGN comparison returns TRUE)
' final return = TRUE when the current row's amount and the paired row's amount have the same sign
' (the IFERROR wrapper catches the case where FILTER returns nothing on an empty table at load time)
=IFERROR(LET(
matches, FILTER(ROW(tblVendorData[@]),
(tblVendorData[Vendor]=[@Vendor])*(tblVendorData[Invoice]=[@Invoice])*(ROW(tblVendorData[@])<>ROW())),
paired_amount, IF(ROWS(matches)>0, INDEX(tblVendorData[Amount], matches-1), [@Amount]),
SIGN([@Amount]) = SIGN(paired_amount)
), FALSE)
' Time-window validation (>30 days indicates split-invoice).
' Same LET pattern as above, except paired_date holds the post-date of the matching row
' and the final return is TRUE when the gap between the two posting dates exceeds 30 days.
' Auditor reading: "Did the second posting land far enough away in time to look like a deliberate
' split invoice rather than a same-day duplicate-payment error?"
=IFERROR(LET(
matches, FILTER(ROW(tblVendorData[@]),
(tblVendorData[Vendor]=[@Vendor])*(tblVendorData[Invoice]=[@Invoice])*(ROW(tblVendorData[@])<>ROW())),
paired_date, IF(ROWS(matches)>0, INDEX(tblVendorData[PostDate], matches-1), [@PostDate]),
ABS([@PostDate] - paired_date) > 30
), FALSE)
' Currency-consistent duplicates only — adds [Currency] match to the COUNTIFS so a USD-EUR
' coincidence on the same vendor+invoice does not get flagged as a duplicate of itself.
=IFERROR(COUNTIFS(tblVendorData[Vendor],[@Vendor],tblVendorData[Invoice],[@Invoice],tblVendorData[Currency],[@Currency]), 0)
Required input columns in tblVendorData for the three formulas above: Vendor (text), Invoice (text), Amount (number, signed), PostDate (date), Currency (ISO-4217 text like “USD” / “EUR”). The auditor pre-maps these from the GL extract or vendor sub-ledger before applying the formulas. Missing the Currency column causes the third formula to throw #N/A; missing PostDate causes the second formula to flag every row as TRUE (default to current row’s date).
In plain English, the first LET block asks: “Do I have another row with this same vendor and invoice, and if so, does it keep the same sign?” The second asks: “Did the second posting land more than 30 days away, which would support a split-invoice explanation instead of an immediate duplicate-payment concern?”
These formulas employ structured table references rather than full-column ranges, ensuring performance at scale. The pattern echoes control testing documented in US v. HealthSouth Corp. (N.D. Ala. 2003), where invoice-duplication analysis revealed systematic revenue manipulation through repeated vendor-side entries with varying amounts.
Excel-Native Synthetic Data Generation
The companion workbook generates 5,000 records with exactly 50 controlled duplicate injections (25 pairs) using Excel formulas exclusively. The practitioner implements the following setup in the workbook:
Workbook Setup Requirements
- Enable dynamic arrays (Excel 365/2021 required)
- Create Name Manager entries for reusable LAMBDAs
- Establish structured tables: tblVendorData, tblEmployeeMaster, tblApprovalLog
- Add Privacy Checklist sheet documenting GLBA compliance
Excel Formula-Based Data Generation
In the DataGeneration sheet:
' Generate vendor codes with Zipf distribution approximation
' A2: Vendor sequence (seed controlled via Analysis ToolPak Random Number Generation with seed=42)
=LET(
vendor_pool, SEQUENCE(500,1,1,1),
weights, EXP(-vendor_pool/100),
cum_weights, SCAN(0, weights, LAMBDA(acc,val, acc+val)),
norm_weights, cum_weights/MAX(cum_weights),
rand_val, RAND(),
vendor_index, XMATCH(rand_val, norm_weights, 1),
"V" & TEXT(vendor_index, "0000")
)
' B2: Invoice numbers
="INV-" & TEXT(RANDBETWEEN(10000,99999),"00000")
' C2: Amounts (log-normal distribution)
=EXP(NORM.INV(RAND(),8,1.2))
' D2: Post dates
=DATE(2024,1,1) + RANDBETWEEN(0,364)
' Inject duplicates at rows 4951-5000 using conditional logic
' E2: Duplication flag
=IF(ROW()>=4951, TRUE, FALSE)
' F2: Source row for duplicates
=IF(E2, 4951+MOD(ROW()-4951,25), "")
' G2: Final vendor (duplicate if flagged)
=IF(E2, INDEX($A$2:$A$4975, F2), A2)
' H2: Final invoice (duplicate if flagged)
=IF(E2, INDEX($B$2:$B$4975, F2), B2)
' I2: Final amount (vary by ±10% if duplicate)
=IF(E2, INDEX($C$2:$C$4975, F2) * (0.9 + RAND()*0.2), C2)
' J2: Final date (offset 5-45 days if duplicate)
=IF(E2, INDEX($D$2:$D$4975, F2) + RANDBETWEEN(5,45), D2)
This approach generates the complete synthetic dataset within Excel, eliminating Python dependencies while maintaining reproducibility through documented seed values (seed=42 via Analysis ToolPak Random Number Generation dialog).
Mid-Test Claude Diagnostic with Chi-Squared Implementation
After initial flagging, the analyst employs Claude for clustering analysis with explicit statistical testing:
Flagged vendor-invoice duplicates (post-calibration):
{paste flagged records with amounts and dates}
Analyze for:
1. Vendor concentration (Pareto distribution?)
2. Amount patterns per PCAOB AS 2401.68 (round numbers, threshold proximity)
3. Temporal clustering (period-end, approval-window gaming)
4. Chi-squared test for temporal uniformity using 12 monthly bins
Output: Risk-ranked investigation sequence with specific AS 2401 paragraph citations.
The practitioner implements the chi-squared test directly in Excel. In plain English, the chi-squared statistic measures the gap between what was actually observed in each monthly bucket and what would have been expected if flagged transactions were spread uniformly across the year — small gap, low statistic, no clustering signal; large gap, high statistic, the data is concentrating in particular months in a way that random variation alone struggles to explain. The p-value below converts that statistic into a probability: a p-value of 0.05 means there is roughly a 5% chance the observed clustering could have arisen from a uniform distribution by random luck alone, which is the conventional threshold below which the auditor concludes the clustering is real and worth investigating.
' Monthly occurrence counts in K2:K13
=COUNTIFS(tblVendorData[PostDate],">="&DATE(2024,ROW()-1,1),tblVendorData[PostDate],"<"&DATE(2024,ROW(),1),tblVendorData[Flag],TRUE)
' Expected frequency (uniform distribution)
' L2:L13
=SUM($K$2:$K$13)/12
' Chi-squared statistic
' M2
=SUMPRODUCT((K2:K13-L2:L13)^2/L2:L13)
' P-value with 11 degrees of freedom
' N2
=CHISQ.DIST.RT(M2,11)
P-values below 0.05 indicate significant temporal clustering warranting investigation per AS 2401.68.
What the practitioner does next should be explicit. If one approver handled 30 flagged transactions in the month and 4 of them occurred outside business hours in the same amount band, the next step is not “fraud proven.” The next step is operational: pull those four approvals, document whether they sit just below an approval threshold, and decide whether AS 2401.85 follow-up means inquiry, expanded sampling, or immediate escalation. The workpaper should document: “Approver JDoe showed 4 of 30 transactions approved 11pm-2am, all $4,800-4,999 range (threshold $5,000). Flagged for AS 2401.85 management override inquiry.”
Worked binomial example. For this JDoe finding, run the binomial probability check: with $n=30$ transactions and the baseline non-business approval rate $p=0.10$, the probability of observing 4 or more non-business approvals in a 30-transaction window is $P(X \geq 4) = 1 – \sum_{k=0}^{3}\binom{30}{k}(0.10)^k(0.90)^{30-k}$. The four cumulative terms work out to 0.0424 + 0.1413 + 0.2277 + 0.2361 = 0.6475, so $P(X \geq 4) = 1 – 0.6475 = 0.3525$ — meaning roughly a 35% chance of seeing 4+ non-business approvals by random scheduling alone. Not yet suspicious in isolation. But when combined with the $4,800-4,999 amount-band concentration (a separate test with its own ~5% baseline), the joint probability collapses to roughly 0.0176 ($0.3525 \times 0.05$), well below the 0.05 investigative threshold. The workpaper records both component p-values and the joint, citing AS 2401.85 for the management-override inquiry rationale.
Employee Master Conflicts Under Privacy Constraints
Payroll fraud exploits the distinction between identity anchors (SSN, employee ID) and peripheral attributes (addresses, bank routing). GLBA §501(b) and state privacy laws restrict SSN access; the engagement team documents authorization under AU-C 240.A41 before accessing protected fields.
Privacy-Compliant Detection Formula
Using employee ID (column A) and hire date (column B) as non-sensitive anchors, with addresses (column C) as the peripheral attribute:
' Wrapped in LAMBDA for reusability with structured tables
=LAMBDA(emp_table,
LET(
id_col, emp_table[EmployeeID],
hire_col, emp_table[HireDate],
addr_col, emp_table[Address],
anchor_matches, COUNTIFS(id_col, [@EmployeeID], hire_col, [@HireDate]),
addr_differs, COUNTIFS(id_col, [@EmployeeID], hire_col, [@HireDate], addr_col, "<>"&[@Address]),
flag, AND(anchor_matches > 1, addr_differs > 0),
flag
)
)(tblEmployeeMaster)
Named as EmployeeConflictTest, this function references structured tables rather than column ranges, eliminating recalculation overhead. Note that this pattern fails in multi-currency environments where address changes coincide with legitimate international transfers — the practitioner should suppress flags for employees with documented cross-border assignments.
Legitimate-Change Suppression via Audit Trail
The practitioner validates flagged conflicts against HR change logs:
=LAMBDA(emp_id, date1, date2,
LET(
log_emp, tblChangeLog[EmployeeID],
log_date, tblChangeLog[ChangeDate],
in_range, (log_emp = emp_id) * (log_date >= MIN(date1, date2)) * (log_date <= MAX(date1, date2)),
SUM(in_range) > 0
)
)([@EmployeeID], [@Date1], [@Date2])
Records with documented changes between conflict dates represent authorized updates; undocumented changes warrant investigation per AS 2401.85.
Post-Test Documentation Through Claude
After filtering to unexplained conflicts, Claude structures the finding:
Employee master conflicts (post-change-log validation):
{paste unexplained conflicts}
Draft per PCAOB AS 2401.85:
1. Ghost employee risk indicators (ACFE Manual §4.301-4.318)
2. Control deficiency classification under AS 2110.59
3. Confirmation procedures with HR and third parties
4. Privacy protocols per GLBA §501(b) and state requirements
Frame as “requiring additional verification” maintaining presumption of regularity.
The output meets professional documentation standards while preserving appropriate skepticism tone. Note that employee-master pattern failures often concentrate during entity restructuring periods — mergers, system migrations, and layoffs create legitimate duplicate-resolution lag that must be distinguished from ghost-employee schemes.
Approval Clustering in Amount Bands and Time Windows
Legitimate approvals distribute across business hours and amount ranges. Circumvention produces clustering: narrow amount bands (just below thresholds), non-business timing, single approvers. The practitioner should note that this pattern produces false positives during year-end processing when legitimate overtime approvals spike — temporal clustering detection should be calibrated to exclude December 15-31 and June 15-30 fiscal closes.
Temporal and Amount-Band Detection
' Create amount bands (nearest $5,000)
=LAMBDA(amount, FLOOR(amount, 5000))([@Amount])
' Business-hours flag (Mon-Fri, 6am-8pm)
=LAMBDA(timestamp,
LET(
dow, WEEKDAY(timestamp, 2),
hour, HOUR(timestamp),
is_business, AND(dow <= 5, hour >= 6, hour <= 20),
is_business
)
)([@Timestamp])
' Count same approver, same band, non-business timing
=LAMBDA(approval_table, current_approver, current_band,
COUNTIFS(approval_table[Approver], current_approver,
approval_table[Band], current_band,
approval_table[BusinessHours], FALSE)
)(tblApprovalLog, [@Approver], [@Band])
Statistical Calibration of Detection Threshold
The threshold of 3+ occurrences emerges from binomial probability analysis. Given baseline 10% non-business approval rate (empirically validated), for an approver processing 30 monthly transactions:
The binomial probability formula asks: given n=30 transactions where each has probability p=0.1 of non-business timing, what is the probability of observing k≥3 non-business approvals? The calculation proceeds as P(X≥3) = 1 – P(X≤2), where P(X≤2) represents the cumulative probability of 0, 1, or 2 occurrences:
$$P(X \geq 3) = 1 – P(X \leq 2) = 1 – \sum_{k=0}^{2} \binom{30}{k} (0.1)^k (0.9)^{30-k}$$
Using Excel’s BINOM.DIST:
=1 - BINOM.DIST(2, 30, 0.1, TRUE)
Result: 0.5836
That result needs translation. Seeing 3 or more non-business-hour approvals out of 30 is not rare by itself. The risk signal appears when those approvals also concentrate in the same amount band or the same approver pattern, because that starts to look like threshold management rather than random timing noise.
For clustering within one of six amount bands, the joint probability calculation demonstrates the escalation from individual occurrence to pattern detection. Under the simplifying assumption that band selection and timing are independent:
| Step | Calculation | Excel Formula | Result |
|---|---|---|---|
| P(3+ non-business out of 30) | Binomial(n=30, p=0.1, x≥3) | =1-BINOM.DIST(2,30,0.1,TRUE) | 0.5836 |
| P(specific band selected) | 1/6 bands | =1/6 | 0.1667 |
| P(3+ in specific band) | 0.5836 × 0.1667 | =0.5836*1/6 | 0.0973 |
The ~10% probability of random occurrence justifies the threshold of 3 for flagging, achieving approximately 90% specificity. The independence assumption warrants validation through entity-specific testing. If the file does not support that assumption, treat the 3-count rule as a screening threshold, not a final statistical claim.
Calibration Benchmarks from Synthetic Testing
Testing on the 5,000-record synthetic dataset with 50 injected duplicates yields:
| Pattern | Raw flags | Post-calibration | Confirmed anomalies | Detection rate |
|---|---|---|---|---|
| Duplicate payment | 127 (2.5%) | 31 (0.62%) | 23 of 25 pairs | 92% |
| Employee conflicts | 43 (0.9%) | 8 (0.16%) | 6 of 10 injected | 60% |
| Approval clustering | 89 (1.8%) | 14 (0.28%) | 11 of 15 injected | 73% |
These rates validate calibration effectiveness: 75-90% reduction in false positives while maintaining 60-92% true positive detection. The 60% employee-conflict detection rate represents the expected lower bound given privacy constraints on SSN-based matching.
Excel Production Implementation Standards
Structured Table Architecture
Convert raw ranges to Tables for formula stability:
' Convert A1:H5001 to Table
' Name: tblVendorData
' Then reference as:
=IFERROR(COUNTIFS(tblVendorData[Vendor], [@Vendor], tblVendorData[Invoice], [@Invoice]), 0)
Named LAMBDA Repository
Centralize test logic in named formulas:
' Name: DuplicateTest
=LAMBDA(vendor_table,
LET(
dup_count, COUNTIFS(vendor_table[Vendor], [@Vendor], vendor_table[Invoice], [@Invoice]),
diff_amount, COUNTIFS(vendor_table[Vendor], [@Vendor], vendor_table[Invoice], [@Invoice],
vendor_table[Amount], "<>"&[@Amount]),
has_variation, AND(dup_count > 1, diff_amount > 0),
has_variation
)
)(tblVendorData)
Dynamic Array Filtering
Excel 365’s FILTER function eliminates manual extraction:
=FILTER(tblVendorData, DuplicateTest(tblVendorData))
This formula spills all flagged records into a dynamic range, auto-updating with source data changes.
What goes into the workpaper
For each flagged same-same-different item, the workpaper should capture:
- the normalized vendor and invoice fields used for matching,
- any credit-memo, currency, or timing filters applied,
- the exact duplicate rows that survived post-filter review,
- the reason the item still appears anomalous, and
- the next procedure selected under AS 2401.67, AS 2401.68, or AS 2401.85.
Example: “Vendor ABC Supply invoice 10492 appeared twice with different amounts and a 37-day posting gap. Credit-memo sign logic did not explain the variance. Item escalated for source-document pull and approver inquiry under AS 2401.85 (management override of controls). Per AS 2110.59, this represents a potential control deficiency in the three-way match process.”
Cross-Pattern Detection Architecture
Same-same-different analysis forms one component of a broader fraud detection suite. Each technique targets distinct manipulation signatures while maintaining operational compatibility through Excel’s structured table architecture. The Benford’s Law implementation provides population-wide frequency screening that identifies systemic manipulation without examining specific record relationships. This article’s pairwise matching detects targeted control failures. The regression residuals approach surfaces continuous-amount outliers that evade categorical tests. Together, these patterns provide overlapping detection coverage as specified in PCAOB AS 2401.67.
Applied as an integrated suite per PCAOB AS 2401.67, these tests provide overlapping coverage while maintaining distinct detection signatures.
References
Audit and forensic-accounting literature:
- ACFE Fraud Examiners Manual (2024 ed.), §1.701-1.735 (Data Analytics for Fraud Detection), §3.301-3.345 (Vendor and Purchasing Schemes), §4.301-4.318 (Payroll Fraud Schemes). Association of Certified Fraud Examiners, Austin, TX.
- Wells, J.T. (2017). Corporate Fraud Handbook: Prevention and Detection (5th ed.). John Wiley & Sons, Hoboken, NJ. Chapter 4, pp. 171-195; Chapter 6, pp. 231-267.
- Albrecht, W.S., Albrecht, C.O., Albrecht, C.C., & Zimbelman, M.F. (2019). Fraud Examination (6th ed.). Cengage Learning, Boston, MA. Chapter 12, pp. 445-478.
- Association of Certified Fraud Examiners. (2024). Report to the Nations: 2024 Global Study on Occupational Fraud and Abuse. ACFE, Austin, TX, p. 47.
Audit standards:
- PCAOB AS 2401 — Consideration of Fraud in a Financial Statement Audit, ¶67 (analytical procedures for fraud detection), ¶68 (unexpected relationships requiring investigation), ¶85 (management override considerations).
- PCAOB AS 2110 — Identifying and Assessing Risks of Material Misstatement, ¶59 (understanding internal control, including entity-level controls).
- AICPA AU-C Section 240 — Consideration of Fraud in a Financial Statement Audit, ¶A41 (examples of fraudulent financial reporting techniques and audit responses).
Privacy and data-protection:
- Gramm-Leach-Bliley Act, 15 U.S.C. §6801(b) (financial institution safeguarding requirements).
- General Data Protection Regulation (EU) 2016/679, Article 5(1)(f) (integrity and confidentiality principle), Article 6 (lawful basis for processing).
- Microsoft Corporation. (2024). “COUNTIFS function.” Excel function reference. Microsoft Documentation.
- Microsoft Corporation. (2024). “LAMBDA function.” Excel function reference. Microsoft Documentation.
- Microsoft Corporation. (2024). “FILTER function.” Excel function reference. Microsoft Documentation.
- Microsoft Corporation. (2024). “BINOM.DIST function.” Excel function reference. Microsoft Documentation.
Companion workbook: synthetic 5,000-record dataset with exactly 50 controlled duplicates (25 pairs), calibration dashboard, privacy checklist, and versioned Claude prompt library.