Duplicates: The Payment Run Came to 39,488.00 and the Invoices Came to 23,081.75

The run was approved on the number at the bottom of column D: 39,488.00, ten lines, four suppliers. Nobody queried it, because ten lines is a small enough list to read and every line on it was a real invoice from a real supplier for a real amount.

Six of those invoices existed. The other four rows were second copies, and they were worth 16,406.25 — 41.5% of the payment run. The invoices actually outstanding came to 23,081.75.

The obvious move is Data → Remove Duplicates, and it was made. It deleted one row, recovered 2,980.75, and left 13,425.50 of double payment on a list that now looked like it had been cleaned.

What this covers. COUNTIF, COUNTIFS, SUMPRODUCT, SUM, TRIM, UPPER and EXACT work in every version this century, and everything essential here is built from them. UNIQUE, FILTER, XLOOKUP and LET need Microsoft 365 or Excel 2021; where one is used, the older equivalent is given beside it. Remove Duplicates, conditional formatting and data validation are menu commands, and their exact wording moves between versions — the behaviour described here does not.

1) Ten Rows, Six Invoices

Here is the run, with what each row actually is:

The ␣ on row 7 marks a trailing space, which is invisible in the cell and is the reason that row survives every tool in section 3. Four rows are extras, worth 4,820.00 + 7,340.00 + 1,265.50 + 2,980.75 = 16,406.25. And each of the four got there a different way, which is the whole reason one tool does not find them all: row 4 was keyed two days later by a different person, row 7 has a supplier name with a space on the end, row 9 is the same invoice keyed a week later, and row 11 is a character-for-character repeat of row 8.

🎯 Scenario: Before deleting anything, put the number on screen. =SUM(D2:D11) is 39,488.00 and section 5 gives you the one-cell formula for 23,081.75. A cleaning step you cannot check against a figure you wrote down first is a cleaning step that reports success whatever it did.

2) Three Different Things Called a Duplicate

The word covers three claims, and they disagree on this data:

An identical row. Every column the same. Only rows 8 and 11 qualify. This is what Remove Duplicates finds when you tick every column, and it is the rarest kind, because any column holding an entry date, a batch number or a user name will differ between two keyings of the same thing.

The same key. The same invoice number, whatever else differs. Four extras, 16,406.25. This is nearly always the definition you want, because the invoice number is the thing the supplier will only be paid once for.

The same-looking row. Same supplier, same amount, nothing else in common. Rows 3 and 6 look like this — Cobalt Print, 1,265.50, three days apart — and they are two different invoices, CP-2207 and CP-2208, for a repeat order. Dedupe on supplier and amount and you delete a real 1,265.50 of cost.

The middle row of that table is the job. The bottom row is worse than it looks: it deletes the real CP-2208, and it still misses the 7,340.00 Nordvik pair, because that supplier's name is spelled two different ways. The other two are what you get when the key is chosen by what is convenient rather than by what identifies the record.

🎯 Scenario: Write down the key before you write a formula, in words: "a duplicate is a second row with the same supplier invoice number." Every technique below is then a way of counting that sentence, and the argument about whether row 6 should go away is settled before it starts.

3) What Remove Duplicates Actually Removed

Select A1:E11, Data → Remove Duplicates, tick all five columns, OK. Excel reports: 1 duplicate value found and removed; 9 unique values remain.

It removed row 11. Nothing else, because nothing else is identical across all five columns — and the run now totals 36,507.25 with 13,425.50 of double payment still in it.

Two behaviours explain the rest, and they are not symmetrical:

• It ignores case. "aldridge freight" and "Aldridge Freight" are the same string to Remove Duplicates. Row 4 survives anyway, because its date is 05/08 and row 2's is 03/08.
• It does not ignore spaces. "Nordvik Supplies " with a trailing space is a different string from "Nordvik Supplies", so rows 5 and 7 both survive — despite being identical in every other column, dates included.

Tick only the Invoice column instead and Excel removes four rows, leaving 23,081.75. That is the right total, and it is still the wrong move on a live payment run for three reasons: it keeps whichever copy came first and silently discards the other, whatever the other one said; it leaves no record that anything was removed; and after the file is saved and closed there is no undo. You have a correct number and no evidence.

🎯 Scenario: Do the Remove Duplicates run on a copy of the sheet, purely to see what the count says — "1 removed" against a ten-row list you believe holds four extras is a measurement, and it tells you the columns you ticked are not the key. Then throw the copy away and flag instead.

4) Flag, Don't Delete

COUNTIF answers "how many rows share this value" for every row at once. In F2, copied down:

=COUNTIF($C$2:$C$11,C2)

Filter that column to >1 and eight of the ten rows come back — every copy of every duplicated invoice, including the first ones, which are perfectly good rows you must not delete. This is also exactly what conditional formatting's built-in Highlight Cells Rules → Duplicate Values does, and it is why that rule produces a screenful of colour and no decision.

The fix is one dollar sign. Anchor the start of the range and let the end travel with the row:

=COUNTIF($C$2:C2,C2)

In row 2 the range is C2:C2, in row 5 it is C2:C5, in row 11 it is C2:C11. The formula therefore counts this row and everything above it, which is 1 on a first occurrence and 2 on a second. So:

=IF(COUNTIF($C$2:C2,C2)>1,"DUPLICATE","")

marks rows 4, 7, 9 and 11 — the four extras and nothing else. As a conditional formatting rule over A2:E11 the same expression, written =COUNTIF($C$2:$C2,$C2)>1, shades exactly the rows to strike off the run.

🎯 Scenario: Keep both columns. The full COUNTIF count tells the buyer how many times PO-8841 has been through the system, and the expanding one tells the payments clerk which line to remove. They answer different questions and they cost one column each.

5) Putting a Number on It

A flag column is useful and a total is what gets the run stopped. Three formulas, none of which need a helper column.

How many distinct invoices are on the run:

=SUMPRODUCT(1/COUNTIF(C2:C11,C2:C11))

returns 6. Each of the two INV-8841 rows contributes 1/2, so the pair contributes 1; the same for every other value, whatever its count. =COUNTA(UNIQUE(C2:C11)) returns the same 6 in Microsoft 365, and does it without breaking on a blank cell — the SUMPRODUCT version divides by zero the moment one cell in the range is empty, which is section 11's trap 4.

What the run should be:

=SUMPRODUCT(D2:D11/COUNTIF(C2:C11,C2:C11))

returns 23,081.75. Same trick, carrying the amount: each of the two 4,820.00 rows contributes 2,410.00, so INV-8841 contributes 4,820.00 once. In Microsoft 365 the same figure comes out of =SUM(XLOOKUP(UNIQUE(C2:C11),C2:C11,D2:D11)), which takes the first amount recorded for each invoice rather than an average — a difference that matters and is trap 3.

What is at risk:

=SUM(D2:D11)-SUMPRODUCT(D2:D11/COUNTIF(C2:C11,C2:C11))

returns 16,406.25, which is the sentence "this run pays 16,406.25 twice" in one cell, updating as rows are added.

🎯 Scenario: Put those three cells at the top of the import sheet, above the data, and label them lines, distinct invoices, duplicated value. A run where the second and third numbers move apart is a run to stop; a run where they are 6 and 0.00 is one nobody needs to read.

6) Two Keys Are Not Safer Than One

The instinct on a payment run is that one key is risky — two suppliers might use the same invoice numbering — so match on supplier and invoice:

=COUNTIFS($A$2:$A$11,A2,$C$2:$C$11,C2)

It finds three of the four extras and misses one. Rows 5 and 7 both return 1, because "Nordvik Supplies " and "Nordvik Supplies" are different criteria to COUNTIFS, and the row it therefore fails to flag is the 7,340.00 one — the largest of the four.

Note which way round the two functions are fussy, because it is counter-intuitive:

Every extra key you add is another column that has to be clean before the match can succeed, and dirty text fails open: the count comes back 1, the row is not flagged, and nothing on screen says a comparison failed. One key that identifies the record beats two that half-identify it.

🎯 Scenario: If you genuinely need two keys — supplier plus their invoice number, which is the correct key when two suppliers may both issue "INV-001" — normalise both before matching, as in section 7. The rule is that a compound key is a single normalised string, not two raw columns compared side by side.

7) Case, Spaces and the Normalised Key

Build the key once, in a helper column G2, copied down:

=TRIM(UPPER(A2))&"|"&TRIM(UPPER(C2))

TRIM removes leading, trailing and repeated internal spaces; UPPER removes the case difference; the pipe stops "AB" & "C" colliding with "A" & "BC". Row 5 and row 7 both become NORDVIK SUPPLIES|NS-5510, and now:

=COUNTIF($G$2:G2,G2)>1

flags all four extras, 16,406.25, on two keys.

The helper column is not optional, and this is the part people lose an afternoon to. COUNTIF and COUNTIFS require a real range as the first argument — a rectangle of cells on a sheet. Hand them a calculated array and you get #VALUE!:

=COUNTIF(TRIM(UPPER(C2:C11)),TRIM(UPPER(C2)))    ✗ #VALUE!

There is no wrapping that fixes it, LET included, because the restriction is on the function rather than on the formula around it. Either write the key into cells, or count with SUMPRODUCT, which is happy with arrays:

=SUMPRODUCT(--(TRIM(UPPER($C$2:$C$11))=TRIM(UPPER(C2))))

And when case is the point rather than the problem — product codes where ab-100 and AB-100 are genuinely different items — EXACT is the comparison that respects it:

=SUMPRODUCT(--EXACT($C$2:$C$11,C2))

On the supplier column of this run that formula returns 1 for both Aldridge rows, which is the wrong answer here: it says a capital letter makes a different supplier. Right function, wrong column — which is the whole point of choosing per column instead of per workbook.

🎯 Scenario: Two habits kill most of this class of bug before it starts. Wrap incoming text keys in TRIM at the point of import, and use =SUMPRODUCT(--(C2:C11<>TRIM(C2:C11))) as a standing check cell — it counts the cells whose text is not equal to its own trimmed version, and on this data it returns 1.

8) The Rows That Only Look Like Duplicates

Rows 3 and 6: Cobalt Print, 1,265.50, both of them, three days apart. That is a repeat order — CP-2207 and CP-2208, two invoices, both payable. Any rule keyed on supplier and amount deletes one of them and underpays a supplier by 1,265.50, and unlike a double payment nobody spots it until the statement chase.

The general shape: a duplicate is a repeated identifier, not a repeated value. Repeated values are what normal business data looks like — the same rent, the same retainer, the same 20-unit order every month.

Three questions settle a candidate row:

1. Does it repeat an identifier — invoice number, order number, employee number, transaction ID? Then it is a duplicate.
2. Does it repeat only measurements — amounts, dates, quantities, names? Then it is probably a real second event.
3. Is there no identifier at all? Then you are deduplicating a judgement, and it belongs in front of a person, not in a formula.

Case 3 is where a flag column earns its keep over deletion: =IF(COUNTIFS($A$2:$A$11,A2,$D$2:$D$11,D2)>1,"CHECK","") puts rows 3 and 6 in front of the buyer with the word check rather than removing one of them and getting it wrong 50% of the time.

🎯 Scenario: When the source has no identifier — bank statement lines, till exports, sensor readings — build one from the fields that together cannot repeat by accident, such as =A2&"|"&TEXT(B2,"yyyy-mm-dd")&"|"&TEXT(D2,"0.00"), and say out loud that two genuine events sharing that key are impossible before you rely on it.

9) UNIQUE, FILTER and the Argument Almost Nobody Uses

In Microsoft 365 and Excel 2021 three formulas replace most of the above, and one of them does something the old functions cannot do at all.

=UNIQUE(C2:C11)              the six invoice numbers, spilled
=COUNTA(UNIQUE(C2:C11))      6
=SORT(UNIQUE(A2:A11))        the supplier list, deduplicated and sorted

UNIQUE's third argument is the one worth knowing. Set it to TRUE and you get the values that appear exactly once, rather than one of each value:

=UNIQUE(C2:C11,,TRUE)        CP-2208, ML-7712

Two invoices on this run were entered once, worth 6,675.50 between them. Everything else on the run was keyed at least twice. That is a stronger sentence than "there are some duplicates", and it is one formula. (The empty second argument is by_col; leaving it out compares rows, which is what you want here.)

To see the offending rows rather than the values:

=FILTER(A2:E11,COUNTIF(C2:C11,C2:C11)>1)

which spills the eight rows belonging to a duplicated invoice, refreshing itself as the source table grows. Note the same caution as section 4 — this returns all copies, first ones included, so it is a review list rather than a delete list.

And the whole diagnosis in one cell, for the header of an import sheet:

=LET(
  key,   C2:C11,
  amt,   D2:D11,
  dist,  COUNTA(UNIQUE(key)),
  waste, SUM(amt)-SUMPRODUCT(amt/COUNTIF(key,key)),
  IF(waste=0,"clean",dist&" invoices, "&TEXT(waste,"#,##0.00")&" duplicated")
)

which on this run reads 6 invoices, 16,406.25 duplicated. COUNTIF still needs key to be a range rather than a computed array, which it is here — the moment you try to TRIM inside the LET, section 7's restriction applies.

10) Stopping the Next One

Everything so far is archaeology. Three things stop the next duplicate instead.

Data validation on the invoice column. Select C2:C1000, Data → Data Validation → Allow: Custom:

=COUNTIF($C$2:$C$1000,C2)=1

Type an invoice number already on the sheet and Excel refuses it. Two limits are worth knowing before you rely on it: pasting bypasses validation entirely — a pasted block is not typed input and no rule fires on it — and validation applies only to the cells it was set on, so rows appended below C1000 are unprotected. Circle Invalid Data (Data → Data Validation → Circle Invalid Data) is the retrospective half of the same feature, and it does find pasted offenders.

Power Query for anything imported more than once. Home → Remove Rows → Remove Duplicates records a step that re-runs on every refresh, so next month's file is cleaned the same way without anybody remembering to do it. Two differences from the worksheet command matter: Power Query's version is case-sensitive, so "aldridge freight" and "Aldridge Freight" survive as two rows unless you upper-case the column first; and Group By with a Count Rows aggregation keeps the evidence rather than throwing it away — a two-column output of invoice and count, filtered to count > 1, is a duplicate report that rebuilds itself. For near-matches like "Aldridge Freight" against "Aldridge Freight Ltd", Merge Queries has a Use fuzzy matching option with a similarity threshold, which is the only tool here that reaches them at all.

A pivot table for the count. Drop Invoice into Rows and Invoice into Values as Count of Invoice, and every value above 1 is a duplicated invoice. For a distinct count — "how many different invoices are on this run" — the option is called Distinct Count, and it appears in the Value Field Settings list only if Add this data to the Data Model was ticked when the pivot was created. That tick box is not obvious and cannot be applied afterwards, which is why most people conclude Excel cannot do distinct counts.

🎯 Scenario: On a recurring import, do all three: Power Query removes the exact repeats on refresh, a COUNTIF flag column catches what survived normalisation, and the three-cell header from section 5 states the money. The formula is the only one of the three that tells you the size of what got through.

11) Twelve Traps

1. Remove Duplicates cannot be undone after a save. It deletes rows with no record of what they were. Copy the sheet first, always.
2. It keeps the first row and drops the rest, in sheet order — not the most recent, not the most complete. If row 4 carried the correct amount and row 2 a typo, you have just kept the typo.
3. SUMPRODUCT(amt/COUNTIF(...)) averages the copies. If the two INV-8841 rows read 4,820.00 and 4,280.00 — a transposition — it returns 4,550.00 for an invoice that is one or the other. =SUMPRODUCT(--(D2:D11<>SUMIF(C2:C11,C2:C11,D2:D11)/COUNTIF(C2:C11,C2:C11))) counts the rows whose amount disagrees with the other copies of the same invoice: 0 on this run, and 2 the day a copy is keyed wrong.
4. SUMPRODUCT(1/COUNTIF(range,range)) returns #DIV/0! if the range has a blank cell, because COUNTIF counts zero blanks. =SUMPRODUCT((C2:C11<>"")/COUNTIF(C2:C11,C2:C11&"")) is the blank-safe version.
5. COUNTIF compares numbers to 15 significant digits. Two different 16-digit identifiers — long bank references, IMEIs, some IDs — count as duplicates of each other. =SUMPRODUCT(--EXACT(range,cell)) compares the text properly; storing them as text with a prefix avoids the question entirely.
6. COUNTIF interprets *, ? and ~ in the value as wildcards. An invoice code containing an asterisk matches things it should not. EXACT again, or escape with ~.
7. COUNTIF coerces text that looks numeric. The text "8841" and the number 8841 are counted as the same value even though VLOOKUP will not match them, so the duplicate report and the lookup disagree about the same two cells.
8. Trailing spaces defeat every text comparison except a trimmed one, and they are invisible on screen. =SUMPRODUCT(--(C2:C11<>TRIM(C2:C11))) counts them; on this data it returns 1.
9. Non-breaking spaces survive TRIM. Text pasted from a web page or a PDF often carries CHAR(160), which TRIM does not touch. =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) does.
10. Conditional formatting's Duplicate Values rule only sees the selected range. A duplicate sitting one row below the selection is invisible, and the rule quietly stops covering rows appended later.
11. The Duplicate Values rule highlights every copy, first included. Eight highlighted rows for four extras is not a delete list; =COUNTIF($C$2:$C2,$C2)>1 as a formula rule is.
12. A pivot table's Distinct Count needs the Data Model, ticked at creation time. Without it the option is not in the list, and Count of Invoice — which counts rows — is silently the wrong number.

Practice

Using the ten rows in the grid above:

1. Flag the extras. In F2:F11, mark only the second and later copies of each invoice number. Which four rows light up, and what do they add to?
2. Both totals in one cell each. Write the formula returning 23,081.75 and the one returning 16,406.25, without a helper column, and say what each returns if a blank row is left in the middle of the data.
3. Fix it deliberately. Delete the trailing space in A7 and re-run the two-key COUNTIFS from section 6. Which row flags that did not before, and how much does the check recover? Then say why lower-casing A4 as well would change nothing.
4. Normalise. Build the compound key in G2:G11 and confirm it flags all four extras. Then write the version that does the same job with no helper column at all.
5. Once only. Return the invoice numbers that appear exactly once, and their total. How many rows of the run does that leave unaccounted for?
6. Stop the next one. Write the data validation rule for C2:C1000, then describe the one way a duplicate still gets in past it.

Summary

A duplicate is not a repeated row — it is a repeated identifier, and almost every wrong answer in this article comes from keying on something that identifies nothing. Rows 8 and 11 are identical and that is the least interesting duplicate on the run; rows 3 and 6 are near-identical and are two real invoices.

So: name the key in words, then count it. COUNTIF with the range anchored at one end marks the extras rather than every copy, which is the difference between a delete list and a wall of colour. SUMPRODUCT(D/COUNTIF) turns the flags into 23,081.75 and 16,406.25, and a number on screen is what gets a run stopped. Normalise the key with TRIM and UPPER before comparing it, because dirty text fails open — the count comes back 1 and nothing tells you a comparison failed. And flag rather than delete, because a row you can see and overrule is worth more than a row Excel removed on a rule you did not write down.

The alternative is the version this article started with: 39,488.00 approved, 23,081.75 owed, and a Remove Duplicates dialog that said one row and meant it.