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. None of these cases is rare: they are the normal way people write a database, not an exception. Whoever entered the second row did nothing wrong: they just 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 is not wrong: it does exactly what you asked, a literal comparison. The problem is that 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 the wrong row when the two are not the same person at all, but just two people with the same name in the same city. A purely textual comparison has no way of telling the two cases apart, and that is exactly where the greatest caution would be needed.

What a real comparison does instead

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 not a deleted row: it 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. And every group carries a level of certainty — certain, probable, ambiguous — so you know which to merge with your eyes closed and which to look at yourself, instead of having to decide row by row as in a spreadsheet.

On a database uploaded as a file the comparison runs on the whole list in a single job, up to a hundred thousand records: no need to sort, group or cross 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.