How to tidy a messy list in three passes

At some point every week someone ends up with a list that needs cleaning: addresses copied out of an email thread, SKUs from two spreadsheets, names collected on a form that let people type whatever they liked. The temptation is to fix it by hand. Past about forty lines that is slower and less reliable than three passes with the right tools, and the order of the passes is what makes the difference.

Pass one: normalise the whitespace

Almost every duplicate that survives a clean-up is a whitespace problem. alice@example.com and alice@example.com with a trailing space are different strings to any tool, as are a line indented with a tab and the same line without. Tabs, double spaces, non-breaking spaces from web pages and blank lines all need to go first.

The whitespace cleaner trims every line, collapses runs of spaces, converts tabs and strips empty lines in one go. If the list came from a document with fancy quotes or the case is inconsistent, this is also the moment to run it through the case converter, because Alice Smith and alice smith are two people until you decide otherwise.

Do this before anything else. Deduplicating first and cleaning afterwards leaves you with duplicates that were only ever separated by a space.

Pass two: remove the duplicates

Now the remove duplicates tool can do its job properly: paste the list, choose whether matching should ignore case, and it returns each line once, in the order it first appeared, with a count of what was removed. That count is worth reading. If a list of 300 came back as 120, something upstream is pasting the same source repeatedly and that is the real fix.

For lists where a line is "the same" only on part of it, such as SKU-1042 blue and SKU-1042 red, split the key onto its own line first with the split lines tool, deduplicate the keys and then rejoin. It is a detour, but it beats eyeballing.

Pass three: sort

A sorted list is easier to scan for near-duplicates the tools cannot see, such as Jon Smith and John Smith, and it is what the person receiving the list expects. The list sorter sorts alphabetically, numerically or by length, ascending or descending, and can shuffle if what you actually need is a random order for a draw. Numeric sort is the one people forget: an alphabetical sort puts 10 before 2, and a list of invoice numbers sorted that way looks fine until someone reads it.

Getting it back where it came from

If the list is going into a spreadsheet, the list to CSV tool turns one-per-line into a single comma-separated line or the reverse, and the join lines tool does the same with any separator, which is how you turn a column of email addresses into something a mail client will accept.

The short version

Clean whitespace, then deduplicate, then sort, in that order. Each pass is one paste and one click, and the result is a list you can trust rather than one you have merely looked at.