Questions · Deduplication

Excel does not find the duplicates: why, and what to do?

What “Remove Duplicates” really does

The function compares the content of the cells you choose, column by column, character by character. It finds the identical row: same text, same capitals, same spaces. Anything that differs even by a detail is not considered equal, and the row stays in the database as if it were another person.

In practice very little is enough to make the comparison fail: an extra space after the house number, “De Vries Jan” instead of “Jan de Vries”, “Kalverstr.” instead of “Kalverstraat”, the postcode written “1012PH” once and “1012 PH” the next time. These cases are frequent: it is the normal way people write a database. Whoever entered the second row wrote the same information in their own words, at a different moment, perhaps from a different form.

The same limit applies to formulas that compare two columns by hand, or a COUNTIF or an EXACT on name and address: they are still a text comparison, and the text of two real duplicates hardly ever coincides character by character.

Why two rows that look different are often the same person

• Capitals and spaces. “JAN DE VRIES” and “Jan de Vries ” (with a trailing space) are two different texts for Excel.
• Abbreviations of the street. “Kalverstr. 92” and “Kalverstraat 92” do not have the same letters in the same positions.
• Surname and first name swapped. An import with the columns exchanged produces “De Vries Jan” next to “Jan de Vries”, and neither of the standard formulas puts them together.
• Initials and equivalent names. “J. de Vries” and “Jan de Vries” do not even share the first name in full.
• A postcode missing on one row only, or written without the space. One empty or differently spelt cell is enough to break an exact comparison on several columns.

Excel does exactly what you asked, a literal comparison, and a real database is never written uniformly.

What you lose by throwing away a row at random

Even when two rows are recognized as equal (for example because one was copied over the other), discarding one at random is risky: if the email was recorded only on one row and the phone only on the other, keeping only one loses a contact detail. And if the choice is manual, on a database of a few thousand rows it becomes a job as long and as error-prone as the one you want to solve: you have to open every suspect pair, compare it by eye, decide which to keep and copy by hand the data missing from the other.

There is also a less visible risk: discarding a row when the two are different people with the same name in the same city. A purely textual comparison has no way of telling the two cases apart.

How RadarAddress deduplication works

RadarAddress deduplication works in two stages. First it fixes every address in its correct form — street in full, postcode in the country's format and confirmed in the national register, city written properly — so two spellings of the same street become the same street even before being compared. Then it compares the names, recognizing initials and abbreviated first names (J. and Jan), surname and first name in the wrong order, a typo in the surname, a prefix written with or without a capital and the title in front of the name, which does not count in the verdict.

The result is a main record that takes the best field from each duplicate, email and phone included, with a note of where every piece of information comes from. Every group carries a level of certainty — certain, probable, ambiguous. In the clean file the certain and probable groups come out already merged; the ambiguous ones stay whole among the rows to review, where you decide.

On a database uploaded as a file the comparison runs on the whole list in a single job, up to a hundred thousand records, without sorting, grouping or crossing sheets by hand.

The same case, two different results

The two rows do not share a single identical string — order reversed, abbreviation, initial, postcode with and without the space — yet they are the same address and the same person. The names are invented for demonstration purposes; any reference to real persons is purely coincidental.