Find duplicates and inconsistent records

Distinguish an exact duplicate record from a legitimate repeat of the same person, build a comparison key that survives spacing differences, recognise a value it cannot repair, and log a keep, drop or repair decision for each row without removing anything from the source.

"Remove duplicates" is the most confidently destructive button in a spreadsheet. It does exactly what it says, against a definition of "duplicate" you did not write and probably would not agree with.

Two rows for the same person are not a duplicate if that person came to two sessions. Two rows with the same name are not the same person if there are two Ana Ruizes. And two rows that look character-for-character identical on screen can be genuinely different values, because one of them contains something you cannot see.

None of that is unusual data. It is what a signup sheet filled in by three volunteers over a week looks like. What separates a usable cleanup from a damaging one is not a better button; it is deciding, in writing, what a duplicate is for this data, before removing anything.

Start by being precise about what is being compared.

A duplicate is a repeated record, not a repeated name. A record is the combination of fields that identifies the thing that happened: for a signup, the person, the session and the date. Two rows are the same record when all three match. If the person matches and the session does not, that is two attendances, and merging them deletes one.

So the first job is to build a record key: the identifying fields joined into one value that can be compared. Joining them with a separator that cannot appear in the data — a vertical bar is a common choice — keeps AB|C from colliding with A|BC.

Before that key can be compared, the parts have to be comparable. Two things are usually in the way, and only one of them is the one people expect.

Case is usually not the problem. A spreadsheet's own text comparison ignores case. In this grid, as in Excel and Google Sheets, "ana ruiz" = "Ana Ruiz" is TRUE, and COUNTIF counts them together. Levelling case with UPPER or LOWER is still a good habit — it makes the key stable if it ever leaves the spreadsheet for a system that does care — but it is not what makes those two rows match here. They already match.

Spacing is the problem. "Ana Ruiz " with a trailing space is a different value from "Ana Ruiz", and "Ana Ruiz" with two spaces in the middle is a third. None of them matches either of the others. TRIM removes leading and trailing spaces and collapses runs of internal spaces to one, which repairs all three at once. It is the necessary half of a comparison key; case levelling is the optional half.

If you need case to matter — comparing codes where AB1 and ab1 are different things — use EXACT, which is case-sensitive here and in both products.

And then there is the character you cannot see. Text pasted from a web page or a document often contains a non-breaking space instead of an ordinary one. It looks exactly like a space. It is not one, and TRIM does not remove it — not here, and not in Excel, which is the behaviour this grid deliberately reproduces. A row like that fails to match anything, silently, and the only way to notice is that two counts disagree: how many rows a reader says are this person, against how many rows the formula matches.

That disagreement is worth building into the sheet as a number, because it is the only alarm you will get.

Finally: do not delete. Decide. Add a column that says keep, drop or repair for each row, with the source sheet untouched beside it. A dropped row is still there to look at when somebody asks why the total changed. A deleted row is gone, and so is the evidence that the deletion was wrong.

Ten signups, entered by three volunteers:

S-01  Ana Ruiz        2026-09-01  CR-101
S-02  ana ruiz        2026-09-02  CR-102
S-03  Ana  Ruiz       2026-09-03  CR-105     two spaces, and one on the end
S-04  Ben Okafor      2026-09-01  CR-101
S-05  Ben Okafor      2026-09-01  CR-101     entered twice
S-06  Cai Bell        2026-09-02  CR-102
S-07  Cai Bell        2026-09-04  CR-106     a second session, not a duplicate
S-08  Ana Ruiz        2026-09-04  CR-106     pasted from a web page
S-09  Dev Nakamura    2026-09-03  CR-105
S-10  Dev Nakamura    2026-09-03  CR-105     entered twice

Four rows say Ana Ruiz. Three of them differ only in case and spacing, and all three describe different sessions, so all three are real attendances that happen to share a person. The fourth, S-08, is also a real attendance — and its name holds a non-breaking space where the ordinary one should be, so its name will not match the others using only TRIM and case levelling.

S-05 and S-10 are the only exact duplicates here: same person, same session, same day, entered a second time by somebody who did not know it had already been entered.

S-07 is the trap. It is Cai Bell again, and it is a different session on a different day. A tool that deduplicates on the name alone deletes an attendance that happened.

Given the rows above, predict these four before revealing them. B2 holds Ana Ruiz, B3 holds ana ruiz, B4 holds Ana Ruiz and B9 holds the pasted version.

=B2=B3
=EXACT(B2,B3)
=TRIM(B4)=B2
=TRIM(B9)=B2

A first attempt at finding duplicates compares names:

=COUNTIF($B$2:$B$11,B2)

Over these rows it reports 2 for both Cai Bell rows and 2 for both Dev Nakamura rows, and treats them the same way. They are not the same: Dev's pair is one signup entered twice, Cai's pair is two signups.

Compare the record instead of the name:

=F2&"|"&C2&"|"&D2

where F holds the normalised name. Now Cai's two rows have different keys, because their dates and sessions differ, and Dev's two rows have the same key. Counting over that column separates the two cases without any judgement being needed:

=COUNTIF($G$2:$G$11,G2)

One more step turns "these rows are duplicates" into "this row is the one to drop". A COUNTIF whose range grows as it is copied down counts occurrences up to and including the current row:

=IF(COUNTIF($G$2:G2,G2)>1,"drop","keep")

The start of the range is anchored and the end is not, so in row 2 it looks at G2:G2, in row 6 at G2:G6, and so on. The first copy of a record sees a count of 1 and is kept; every later copy sees more and is dropped. Exactly one row of each pair survives, and which one is a rule rather than a mood.

A colleague ran Remove Duplicates over the attendance list. It reported that 31 rows were removed, and the term's attendance total fell by 402. They ask whether that is right.

Those two numbers alone do not establish whether the removal was right. Check the record identities and the formula behind the attendance total.

1. Ask what it compared. Remove Duplicates deduplicates on the columns you tick. If only the name column was ticked, it kept one row per person for the whole term. That is not a duplicate removal; it is a conversion of an attendance log into a membership list.
2. Reconstruct the excess copies from the source. On an untouched copy, build the record key and count copies after the first in each group, not every row belonging to a repeated group. Then compare those specific row IDs with the removed rows. Even a matching total of 31 is not proof that the right rows were removed.
3. Recompute the total using its actual definition. A reduction of 402 across 31 rows averages about 12.97 per row. If the total is simply a count of rows, removing 31 rows reduces it by 31. If it sums a numeric attendance field, removing genuine duplicates can reduce that sum by 402. The size of the change alone cannot distinguish correct cleanup from lost records.
4. Check for lost legitimate repeats. Compare the removed row IDs with the source. The same person in a different session is a different record under this definition; a name-only key would merge those records.
5. If the cleanup was wrong, restore from the source and start again with a key. Do not try to re-add missing rows by hand. This is what the untouched sheet is for.

The general shape: a deduplication is only as good as its definition of identity, and the button never asks you for one. Write the key yourself and the definition becomes visible, arguable and correctable.

The Signups sheet is the record of what was entered and stays untouched. The Working sheet mirrors it and is where you build the comparison.

Build the normalised name, the record key, the count of rows sharing each key, and the keep-or-drop decision. Then complete the findings, including the two counts whose disagreement reveals the row you cannot see anything wrong with.

A supplier list holds these three entries, each with orders attached:

Ridgeline Supply
Ridgeline Supply Co.
RIDGELINE SUPPLY CO

Answer four questions on paper before touching a grid.

1. Would a spreadsheet's ordinary equals sign treat any pair of these three as the same value? Compare each pair and explain which characters matter.
2. TRIM and UPPER will not merge all three. Name the thing that stops them, and say why it is not a spacing problem.
3. Suppose you merge all three into one supplier. Give one specific way that could be wrong, and one specific figure it would change.
4. What would you need to see, outside this list, before merging them?

Sign in to track your progress on this exercise.

There is one job here an assistant does well and one it cannot do at all.

Useful: "What invisible characters could make two strings that look identical fail to match?" You will get a good list — non-breaking space, zero-width space, a different kind of hyphen, a right-to-left mark — and, more useful still, the formulas to detect them. That answer is checkable in ten seconds: LEN on both values, and CODE on the character in question if this grid supported it. If the lengths differ and the text looks the same, the assistant's list gave you somewhere to look.

Not useful: "Are these two suppliers the same company?" An assistant cannot know, and a plausible answer here is worse than no answer, because it is exactly the judgement that needs external evidence.

The dividing line is a good one to carry: ask an assistant about the mechanism, decide the identity yourself.

Move on when you can do all of these.

• Say what a duplicate is in terms of fields rather than in terms of names, and name the field that separates the two Cai Bell rows.
• Say whether "ana ruiz" = "Ana Ruiz" is TRUE in a spreadsheet, and name the function to use when you need case to matter.
• Explain what TRIM does and name one thing that looks like a space and survives it.
• Describe the formula that marks the second and later copies of a record without marking the first, and say which part of its range is anchored.
• Explain why a cleanup column beats deleting rows, in one sentence, to somebody in a hurry.

Sign in to track your progress on this exercise.

A duplicate is a repeated record, and you decide what a record is. Build the key, count with it, mark rows rather than deleting them, and keep a visible count of the rows that look like a match and are not — because that count is the only warning you will get about the ones you cannot see.

Next: Validate, sort, filter, flag. The data is now as clean as inspection can make it. The remaining question is how to stop it getting dirty again, and how to sort and filter it without pulling records apart.