Content authored by practitioners with experience at Amazon India, Intuit QuickBooks, and the Tata Group. Meet the team →
A payment register may contain the same invoice paid twice, typed slightly differently each time.
Build a cleaned key by removing separators and spaces and converting to upper case, count each key with COUNTIFS, and also count vendor plus amount to catch payments with different invoice numbers.
A payment register with date, vendor, invoice number and amount, plus helper columns for the cleaned key and the two counts.
Every row whose key appears more than once is flagged for review without any row being deleted.
Add a helper column that counts how often each invoice number appears, and filter for counts above one. For payments without a reliable invoice number, count vendor plus amount instead. Clean the key first so “AB/241” and “AB-241” are treated as the same. Use Remove Duplicates only on a copy, because it deletes rows.
Every formula here works in Excel 2010 and later, including Microsoft 365.
Which key identifies a duplicate?
| Key | Catches | Where it fails |
|---|---|---|
| Invoice number | The same bill entered twice | INV-12 against INV/12 or “INV 12”; two vendors who both use invoice 101 |
| UTR | The same bank transfer recorded twice in the books | Two genuinely separate payments for one invoice have different UTRs, so it misses them |
| Vendor plus amount | Same bill paid twice under different references | Genuine repeats, such as monthly rent of ₹45,000 |
| Vendor plus amount plus date | Payments keyed twice on the same day | Misses a repeat a few days later |
In practice, use two keys: vendor plus cleaned invoice number, and vendor plus amount. Review what either one flags.
How do I build the helper columns?
Say the register runs from row 2 to row 15, with date in A, vendor in B, invoice number in C and amount in D.
Cleaned invoice number (column E):
=UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(C2),"-",""),"/","")," ",""))This turns “AB/241”, “AB-241” and “ab 241” into “AB241”.
Count of vendor plus cleaned invoice (column F):
=COUNTIFS($B$2:$B$15,B2,$E$2:$E$15,E2)Count of vendor plus amount (column G):
=COUNTIFS($B$2:$B$15,B2,$D$2:$D$15,D2)Filter F or G for values greater than 1. Those rows need a look.
To colour duplicates instead, select E2:E15, go to Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values. This only colours single-column duplicates, so keep the COUNTIFS columns for the vendor-level checks.
Worked example: a 14-line June register
A trading company’s June payment register has 14 lines. These are the six that matter; the other eight are single payments to different vendors.
| Row | Date | Vendor | Invoice no. | Amount | Count on raw C | Cleaned E | F | G |
|---|---|---|---|---|---|---|---|---|
| 3 | 4 Jun | Arun Brothers | AB/241 | ₹1,86,400 | 1 | AB241 | 2 | 2 |
| 6 | 7 Jun | Kiran Steel | KS-88 | ₹72,000 | 1 | KS88 | 1 | 1 |
| 8 | 10 Jun | Metro Rent | JUN-RENT | ₹45,000 | 1 | JUNRENT | 1 | 1 |
| 10 | 15 Jun | Kiran Steel | KS-91 | ₹72,000 | 1 | KS91 | 1 | 2 |
| 12 | 19 Jun | Arun Brothers | AB-241 | ₹1,86,400 | 1 | AB241 | 2 | 2 |
| 14 | 25 Jun | Sai Packaging | SP/1190 | ₹23,600 | 1 | SP1190 | 1 | 1 |
A simple =COUNTIF($C$2:$C$15,C2) on the raw invoice number gives 1 for every row. “AB/241” and “AB-241” look different to Excel, so the duplicate is missed.
On the cleaned key, rows 3 and 12 both become AB241, and column F shows 2. The same ₹1,86,400 bill was paid on 4 June and again on 19 June.
Column G also flags Kiran Steel: two payments of ₹72,000. But the invoice numbers differ (KS-88 and KS-91) and column F shows 1 for each. Kiran Steel supplies the same order every week, so these are probably genuine. Check the two invoices before you clear them.
The numbers in one place:
- The six lines shown total ₹5,85,400; the other eight lines total ₹3,92,000; the June register is ₹9,77,400.
- The four rows flagged by column G carry ₹5,16,800 (two Arun Brothers payments of ₹1,86,400 and two Kiran Steel payments of ₹72,000).
- Of that, the first Arun Brothers payment is the genuine one and Kiran Steel’s ₹1,44,000 is two real weekly bills. Only one ₹1,86,400 payment is a true duplicate.
That is why a flag is a question, not an answer. Every flagged row needs a look at the underlying invoice.
How do I avoid false alarms from rent, salary and EMIs?
- Add the month to the key. For a register covering several months, count vendor plus amount plus month: add a column with =TEXT(A2,“yyyy-mm”) and include it in COUNTIFS. Rent of ₹45,000 paid in June and July is then counted separately.
- Keep a recurring payee list. Landlord, loan EMI, payroll transfers. Filter them out of column G, but never out of column F; a rent invoice number entered twice is still a duplicate.
- Look at the gap between dates. Two equal payments a month apart are usually recurring. Two equal payments within 20 days are worth checking.
What to read next
Next, read how to highlight mismatches between two columns in Excel. Finding a duplicate is the easy half; for why it happens and how to prevent it, see missing and mistimed entries and manual detection techniques for reconciliation. Related: account reconciliation software for India.