Still Duplicated After Deduping? Five Data Cleaning Traps

#Work out which kind of duplicate you have

"Still duplicated after deduping" means one of two things: the comparison rule never covered the difference, or the duplicate is not inside a single line. Treating those as one problem makes every tool look broken.

The test is simple: copy both suspect lines and compare them character by character.

#Whole-line comparison: what counts as a duplicate

Comparison works on whole lines: two lines are duplicates only when they match exactly after trimming. There is no similarity matching, punctuation is not ignored, and word order matters - "apple banana" and "applebanana" are two different lines.

With the text deduplicator, three behaviours decide the output:

• Both comparison and output trim, so leading and trailing spaces disappear
• Order follows first appearance and is never re-sorted alphabetically
• With case-insensitive mode on, the first occurrence's casing wins: Apple first means Apple in the output

So the result is a clean list in first-appearance order with original casing. For alphabetical or numeric order, run the sorter afterwards.

#Deduping will touch your indentation

Trimming is necessary for deduping - without it a trailing space creates a false duplicate - and the price is losing indentation. That trade is exactly right for lists and exactly wrong for code.

The test: does the indentation in this text carry meaning?

• Lists, tags, IDs, URLs: indentation is decorative, safe to use
• Code, YAML, Markdown lists, SQL: indentation is syntax, using this tool breaks it

For structured text, dedupe inside the editor, or use a tool that compares trimmed lines but preserves them. This is also why general text tools never replace formatters - they care about different things.

#Sorting: why A comes before a

Plain sorting compares UTF-16 code points, and uppercase letters have lower code points than lowercase (A is 65, a is 97), so Apple sorts before apple. Not a bug - an inevitable result of code point comparison.

Three cases with the text sorter:

• Case should not affect order: enable case-insensitive sorting
• Chinese: sorts by Unicode code point, not pinyin or stroke count. Pinyin order needs transliteration first
• Sorting is stable: equal keys keep their original relative order instead of shuffling

The sort key is the trimmed line, and the output is that same version, so outer whitespace disappears. With the blank-line filter off, blank lines sort as empty strings and pile up at the top in ascending order.

#What natural sorting fixes

Plain sorting puts item10 before item2 because it compares character by character and 1 is less than 2. Human reading expects the opposite, which is why natural sorting exists.

Natural sorting splits each line into alternating digit and non-digit runs and compares digit runs by numeric value:

Version numbers, page numbers, numbered products and chapter titles all want natural sorting. Plain names and addresses are fine with plain sorting - no digit runs means identical results.

#Bulk replacement: what regex is left for

Deduping and sorting only handle whole lines; changes inside a line need replacement. This comes up constantly when cleaning tables copied from a web page: extra tabs, bracketed notes, a uniform prefix.

Validate the pattern with the regex tester, then run it through find and replace:

# Strip a trailing marker such as " (archived)" from each line
Find:    \s*\(archived\)$
Replace: (leave empty)

Three frequent regex traps:

• . does not match newlines: use the s flag or [\s\S]*? for multi-line spans
• ^ and $ are not per-line by default: enable multi-line mode
• \d does not match fullwidth digits: １２３ pasted from Excel will not match, convert it first

A dollar sign in the replacement is always special regardless of mode, so a literal one needs $$.

#Which tool to use

Suggested cleaning sequence: count lines for a baseline, normalise fullwidth characters, drop blank lines and dedupe, sort if needed, then finish with in-line replacements. Keep each intermediate result so a bad step is easy to identify.

#Frequently asked questions

#Two lines look identical but both survive. Why?

Compare character by character first; a trailing space or tab is the usual culprit. If it is not that, look for fullwidth versus halfwidth forms (１２３ versus 123), non-breaking spaces (a pasted   becomes U+00A0 and looks like an ordinary space), or zero-width characters. Checking the character count with the text counter is the fastest way to spot them.

#Why is Chinese not sorted by pinyin?

Because sorting uses Unicode code points, which have nothing to do with pronunciation. Pinyin order requires transliterating each character, sorting the pinyin strings, then mapping back - native browser sorting cannot do it.

#Why did the line count grow after deduping?

Check whether "show counts" is enabled: it prefixes every line with a count and two spaces, so the output is no longer pure data. Feeding that back in treats each annotated line as a new, unique entry.

#How do I delete only blank lines?

The deduplicator removes blank lines by default. To remove only blank lines while keeping duplicates, use find and replace in regex mode: find ^\s*$\n? and replace with nothing, with multi-line mode enabled.

#Reversing turned my Chinese into garbage.

Wrong mode. Character reversal reverses newlines too, so lines get interleaved and misaligned, and it splits by code point, which breaks multi-code-point sequences such as skin-toned gestures and family emoji joined by zero-width joiners. For "write each line backwards", use per-line reversal.