Excel Blog
Every guide we've written, laid out as a workbook. Filter it from the formula bar, sort a column, arrow down the rows — the reading opens beside them.
Duplicates: The Payment Run Came to 39,488.00 and the Invoices Came to 23,081.75
Ten rows went into a supplier payment run totalling 39,488.00. Six invoices were on it, worth 23,081.75, and the other 16,406.25 was four rows that had already been paid — 41.5% of a run that Excel's own Remove Duplicates looked at and deleted exactly one row from, because one supplier name carried a trailing space, one carried a different capital letter, and two copies of the same invoice had been keyed on different days. This guide covers duplicates end to end: the three different things people mean by the word, and why a tool that answers one of them cannot answer the others; what Remove Duplicates actually compares, which columns it forgives and which it does not, and why it is the wrong first move on data you have not yet measured; flagging with COUNTIF and the expanding-range trick that marks the second copy rather than both; putting a number on it, with SUMPRODUCT(D/COUNTIF) returning the 23,081.75 that should have been paid and the subtraction that returns the 16,406.25 that should not; why a two-key COUNTIFS check found three of the four and missed 7,340.00; TRIM, UPPER and a normalised helper key, plus the reason COUNTIF cannot take that key as an array; EXACT for the codes where case is the whole point, and the 15-digit comparison that makes COUNTIF call two different long IDs the same; UNIQUE, FILTER, XLOOKUP and the third argument of UNIQUE that returns only the rows appearing exactly once; data validation that refuses the next one, the paste that walks straight through it, and the Power Query step that re-runs on every refresh — case-sensitively, unlike everything else in Excel; and twelve traps, from the invoice code containing an asterisk to the pivot table that will not count distinct until you tick a box nobody mentions.
- 💸 Ten rows, 39,488.00 of payment run, 23,081.75 of actual invoices — the 16,406.25 in between is four rows that had been entered twice, and every one of them looked like a normal line on screen
- 🧹 Remove Duplicates with every column ticked deleted one row and recovered 2,980.75 of the 16,406.25, then reported success — it forgives a capital letter and refuses to forgive a trailing space, and it cannot be undone once the file is saved
- 🚩 =COUNTIF($C$2:$C$11,C2) lights up eight of the ten rows, which is the wrong answer to "which ones do I delete?"; =COUNTIF($C$2:C2,C2)>1, with the range anchored at one end only, marks exactly the four extra copies
That formula is real: the sheet below is its spill range, and it recalculates as you type.