Duplicate Records

A duplicate record is a row that repeats information already present in another row. They arrive when datasets are combined, when several people enter the same item, or when an export runs twice, and their cost is arithmetic: a membership fee recorded twice is revenue that does not exist, and repeated rows change counts, totals, and the weight given to values in an average. In a hypothetical two-value set, 10 and 20 average 15; repeating 10 lowers the mean to about 13.33, while repeating 20 raises it to about 16.67.

What a spreadsheet counts as a duplicate is narrower than the everyday meaning. Microsoft’s definition for Remove Duplicates is that “a duplicate value is one in which all values in at least one row are identical to all of the values in another row”, and the comparison uses displayed values. Formatting can affect the comparison: the same stored date displayed in two different formats can be treated as two unique values. Choose the comparison columns and check representative text and formatted values before deleting; do not assume that a visible case or spacing difference has the same treatment in every tool.

That makes the order of operations matter. Standardize text first — trim spaces, unify case, fix formats — because cleaning after deduplication leaves duplicates the tool already declined to remove. Then look before deleting: Microsoft advises to “filter for unique values first to confirm that the results are what you want before you remove duplicate values”, and to copy the range elsewhere beforehand, since the deletion is permanent once undo is gone. COUNTIF against a column shows how many times each value occurs without changing anything.

The harder question is not mechanical. Two rows describing one person with different identifiers from two systems are not a duplicate to be deleted; they are a matching problem, and discarding one destroys the record of what each source knew. Decide what makes a row the same thing — which columns constitute identity — before running a tool that answers that question for you by comparing all of them.

References:

• Filter for unique values or remove duplicate values
• Top ten ways to clean your data