The Number 1 and the Text "1" Are Not Duplicates

Where the mixed types come from

Almost always from an import. A CSV has no types at all — every field is text until something decides otherwise — so the program that opened it guessed, and it guesses differently depending on the column's first rows, the locale and whether a value has a leading zero or a currency sign. Reference numbers are the usual casualty: 00412 is text in one file because the zero must be kept and the number 412 in another because it looked numeric. Merge those two and the same reference is two rows.

The other source is invisible whitespace. A trailing space survives a copy-paste from a web page, a PDF or an email, and it makes two otherwise identical cells different for every exact comparison — deduplication here, but also lookups, joins and pivot tables everywhere else. Cleaning it is a find-and-replace before the merge, and it is worth doing on any column you intend to match on.

Whole rows, not a key column

Comparing the whole row is the conservative choice and worth understanding as one. Two rows that agree on the reference number but differ in one address field are kept, because the tool cannot know which of the two addresses is current — deciding that is a judgement about your data, not an operation on it. If you want to deduplicate on a single column, sort by that column first and remove the extras yourself; the result is the same and the decision stays yours. The header row is never treated as a duplicate of anything, so a file whose first row repeats a value from the body keeps both.

Frequently asked questions

Which of the duplicates is kept?

The first one in the file, in the order the rows appear. Everything after it is dropped. If the rows differ in a column you did not think to look at, the one kept is the earliest, not the best — so if recency matters, sort the sheet before you deduplicate rather than after.

How do I make a mixed column one type?

Decide which type the column really is and convert the whole thing in one operation before merging. Reference numbers, postcodes and phone numbers are text — they have leading zeros and are never added up. Amounts and counts are numbers. In a spreadsheet, format the column, then use a text-to-columns pass or a helper column with TEXT or VALUE to force every cell across; formatting alone does not change what is stored.

Does it look across the sheets of a workbook?

It works on one sheet at a time, which is what you want when tabs hold different things. To deduplicate across several files or tabs, merge them into a single sheet first — the row merge stacks every file's rows into one table and keeps only the first header — and then run this on the result.