Excel Comparison12 min read

Finding Duplicates Between Two Excel Files: The Combined-Count Test

A duplicate is rarely two identical rows in one sheet. It is the same invoice recorded once in each of two systems — which is why the test has to count both sides together.

Short answer

To find duplicates between two Excel files, count each key on both sides and add the counts together. A key with a combined count greater than one appears in both files, which for an invoice, voucher or payment reference usually means it has been recorded twice. In Excel that is =(COUNTIF(ThisSheet!$A:$A,$A2)+COUNTIF(Other!$A:$A,$A2))>1. Testing a single side will miss the most common duplicate of all — the same document entered once in each file.

Two different problems are both called duplicates

	Within one list	Across two lists
What it looks like	The same row appears twice in the same sheet	The same document appears once in each of two files
Typical cause	A paste done twice, or an import run twice	The same invoice booked in two systems, or paid twice
Test	`=COUNTIF($A:$A,$A2)>1`	`=COUNTIF(Here!$A:$A,$A2)+COUNTIF(There!$A:$A,$A2)>1`
Excel's built-in tool	Highlight Duplicate Values will find these	It will not — the values are not duplicates within either file

The second column is the one that causes real damage. A vendor invoice entered in your purchase ledger and separately in the payment sheet, with the payment then released against the second entry, is a double payment. Neither file contains a duplicate — the duplication only exists across the two.

Why testing one side does not work

This is the part worth understanding rather than memorising. A COUNTIF against the other file returns 1 when the key exists there. But 1 is also what a perfectly correct match returns. The formula cannot distinguish “this document is correctly present on both sides” from “this document has been entered twice”, because both produce the same count.

Count both sides and add

Combining the two counts is what makes the test work. A key present once on each side gives 1 + 1 = 2, and 2 is greater than 1, so it is flagged. A key present once on one side only gives 1 + 0 = 1 and is not flagged. A key present once on one side and twice on the other gives 1 + 2 = 3 — a duplicate within a file that is also present in the other.

The combined-count test, built out

Column	Formula	What it gives you
Count in this file	`=COUNTIF($A:$A,$A2)`	How many times the key appears here
Count in other file	`=COUNTIF(Other!$A:$A,$A2)`	How many times it appears there
Combined	`=B2+C2`	Total occurrences across both
Duplicate?	`=IF(D2>1,"Duplicate","Unique")`	The flag to filter on
Where it appears	`=IF(AND(B2>0,C2>0),"Both files",IF(B2>0,"This file only","Other file only"))`	The location, which determines what you do about it

The last column is what makes the output actionable. A duplicate that exists in both files needs one of the two entries reversed; a duplicate that exists twice within one file needs the row removed. The finding is the same word; the remedy is not.

A worked example

Your purchase ledger and the payment register for August. The same invoice appears in both, because the invoice was booked when it arrived and again when the payment was released.

Invoice	Purchase ledger	Payment register	Combined	Finding
KP/2025/1874	1	1	2	In both files — check whether it has been paid twice
KP/2025/1902	1	0	1	Unique
KP/2025/1955	1	1	2	In both files
KP/2025/1988	2	0	2	Entered twice in the purchase ledger
KP/2025/2011	1	0	1	Unique
KP/2025/2024	0	1	1	Unique — in the payment register only

Two different duplicate types in one list, and they need different corrections.

Note the difference between row one and row four. In row one the duplication is across the files, which is normal if the two registers are meant to hold different things — the payment register recording payments, not invoices. Row four is a genuine duplicate within one file, and the entry has to be reversed.

Not every cross-file match is a duplicate

If the two files hold different information about the same transaction, a key appearing in both is correct. Before you run the test, be clear about what each file is for. A duplicate list that flags every correctly-matched record is worse than no list at all, because it trains people to ignore it.

Power Query: grouping to find duplicates at scale

Beyond a few thousand rows the COUNTIF approach becomes slow, and Power Query does the same job by grouping.

Load both files as queries and append them

Append the two tables into one with a column recording which file each row came from. This source column is what lets you tell the two sides apart afterwards.

Group by the key and count the rows

Group By the key column with an operation of Count Rows. Every key with a count greater than one is a duplicate, in exactly the sense of the combined-count test.

Filter to counts greater than one

That filtered table is your duplicate list — a key and how many times it appears, with no helper columns left in your source sheets.

Merge back to see the rows themselves

Merge the duplicate list against the appended table on the key and expand the columns, so each duplicate row is visible with its amounts, dates and source file.

Power Query also offers a fuzzy merge for near-duplicates, where the key is similar rather than identical — a vendor name spelled two ways, or an invoice number with a character transposed. It works by a similarity threshold you set. Treat its output as suggestions: at a loose threshold it will pair unrelated records, and at a tight one it will miss the very variants you were looking for.

Remove Duplicates: read this before using it

Excel's built-in Remove Duplicates is fast, convenient, and dangerous in equal measure.

What it does	The problem
Removes rows immediately	There is no preview and no undo once the file is saved
Keeps the first occurrence	Which row is “first” depends on the current sort order, so the row that survives is arbitrary
Operates on a single range	It cannot see another file, so it cannot help with cross-file duplicates at all
Considers only the columns you tick	Tick too few columns and genuinely different rows are treated as duplicates

Never run it on a source sheet

Copy the data to a working sheet first, add the count columns, mark the duplicates, review the marked list with whoever needs to see it, and only then remove anything — on the copy. The version of a duplicate that you deleted is also the version that tells you what went wrong at the point of entry.

Deciding which row to keep

Confirming that something is a duplicate is the easy half. Deciding which entry survives is a judgement, and it should be made by a rule rather than case by case.

Rule	When it is the right one
Keep the earliest record	The later entry is the erroneous one — common for a re-import
Keep the record from the primary system	One file is the book of record and the other is a working list
Keep the record with the supporting document	The document reference is the evidence; keep the row that has it
Keep the higher amount, if the two differ	Only where the difference is explained by an amendment — otherwise you are hiding a real discrepancy
Escalate rather than decide	Where the duplicate involves a payment that has already left the bank

Whatever rule you choose, write it at the top of the working sheet with the date and who applied it. A duplicate list cleared without a recorded rule is very hard to defend when it is questioned a year later.

Prevention is cheaper than detection

Validate at entry. A data validation rule or a simple COUNTIF check on the entry form stops most duplicates before they exist.
Reconcile monthly, not annually. A duplicate found in the same month it was created can be reversed cleanly. One found eleven months later usually involves a payment.
Keep the key visible. Duplicates thrive where a document is identified by description rather than by number. If a row has no reference, give it one.
Check imports twice. Running the same import file twice is the most common cause of a whole-block duplicate, and it is invisible unless you check the record count before and after.

Common mistakes

Testing only one side. The most common duplicate in accounting exists once in each file, and a single-sided test never sees it.
Treating every cross-file match as a duplicate. If the two files hold different things, matching keys are the normal case.
Using Remove Duplicates on source data. It removes rather than reports, and it keeps whichever row happens to come first.
Ignoring case and stray spaces. A duplicate whose key differs only by a trailing space will not be detected by any of these tests. Normalise both keys first, with the same formula.
Deduplicating before comparing. If you remove duplicates first, the comparison that follows has no way to tell you they existed.
Not keeping the evidence. Export the duplicate list before clearing it. The list is your proof that a control was performed.

When the duplicate list gets too long to review

A short duplicate list is a control. A list of four hundred duplicates is a process problem that no amount of spreadsheet work will fix — the cause is upstream, in how records are being entered or imported, and the useful action is to fix that.

Where the volume is genuinely a matter of transaction count rather than process failure, the mechanical part — counting across files, normalising the keys, flagging the pairs — is what a tool does well. The Piloteq Automate comparison workflow runs duplicate detection across both files and reports the records rather than removing them, which keeps the decision about which entry survives with the person who should be making it.

If you would rather not build this by hand

Find duplicates without deleting anything

Piloteq Automate runs duplicate detection as one of its match passes and reports the records rather than removing them, so you can review each one before anything is changed in your books.

✓Duplicate detection across both files, not just within one
✓Combined counts shown per key
✓Exact and last-digit matching rules
✓Results as a reviewable list with reasons

₹1,499 · Single PC License · 12-month license

See how Piloteq Automate handles duplicates →

Frequently asked questions

How do I find duplicates between two Excel files?+

Count the occurrences of each key on both sides and add the counts together. A key whose combined count is more than one appears on both sides, which for an invoice number or a voucher number means it has been recorded twice. The formula is =(COUNTIF(ThisSheet!$A:$A,$A2)+COUNTIF(Other!$A:$A,$A2))>1. Testing only one side will miss the most common case, where the same document exists once in each file.

Why is a simple COUNTIF not enough to find duplicates across two files?+

Because COUNTIF against the other file returns 1 for a key that exists there, and 1 is what a correct match looks like too. The duplicate case is a key that exists on both sides when it should exist on only one. That is why the test has to combine the counts from both sides rather than check just one.

Does Remove Duplicates in Excel find duplicates across two files?+

No. Remove Duplicates works within a single range or table, and it removes rows rather than reporting them. It also acts immediately and, once you save, the removed rows are gone. For anything involving accounting records, mark the duplicates in a copied sheet and review them before removing anything.

Can Excel find near-duplicates, such as the same vendor name spelled differently?+

Not with a standard formula. Power Query has a fuzzy merge option that matches similar text using a similarity threshold, which can catch spelling variants of a vendor name. It is a starting point rather than an answer — a low threshold produces false matches, and a high threshold misses the variants you were trying to catch. Every fuzzy result needs a person to review it.

How do I decide which duplicate row to delete?+

Decide the rule before you look at the list, then apply it consistently. Common rules are to keep the earliest record, the one from the primary system, or the one with the supporting document attached. What matters is that the rule is written down and applied the same way throughout, because a duplicate list cleared inconsistently is harder to defend than one that was never cleared.

How do I prevent duplicates appearing in the first place?+

Validate the key at the point of entry rather than at the end of the month. A data validation rule that rejects a key already present in the current period catches most of it. Where the entry happens in a different system, the practical control is the same key test in your reconciliation routine — find them monthly, while the person who entered them still remembers why.