Duplicate rows, but duplicate by which columns?

Removing duplicate rows looks like a one-click job, and the click is not the hard part. The hard part is a question almost nobody asks first: duplicate by which columns? A row can be a duplicate on one column and a perfectly good, distinct record on the whole row. Pick the wrong key and nothing goes wrong on screen. You get a smaller, tidier-looking file with real records missing from it.

Four rows, and one of them is a trap

Here is a small order export. There is nothing strange about it.

• order_id,email,item,amount
• 1041,[email protected],Keyboard,49.00
• 1042,[email protected],Mouse,19.00
• 1043,[email protected],Monitor,180.00
• 1043,[email protected],Monitor,180.00

One row is genuinely duplicated: order 1043 appears twice, identically. That is the row you want gone.

Now de-duplicate with the key column set to email. This is the real output:

• 1041,[email protected],Keyboard,49.00
• 1043,[email protected],Monitor,180.00
• 2 unique row(s); 2 duplicate(s) removed (kept the first of each).

Order 1042 is gone. Ana bought a keyboard and a mouse in two separate orders; on the key email the second order is a duplicate of the first, so it was removed. The order total went from 49.00 + 19.00 + 180.00 = 248.00 to 49.00 + 180.00 = 229.00. Nineteen dollars of real sales disappeared and the tool reported success.

Run the same file with the key left blank, which compares the whole row:

• 1041,[email protected],Keyboard,49.00
• 1042,[email protected],Mouse,19.00
• 1043,[email protected],Monitor,180.00
• 3 unique row(s); 1 duplicate(s) removed (kept the first of each).

That is the right answer, and the count told you so before you looked: one removed, not two.

The method: match the key to the grain

Every file has a grain — the thing one row is one of. This file is one row per order, so the key that means “the same thing twice” is the order id or the whole row, never the customer. A mailing list is one row per person, so there the email is exactly right. Write the grain down before you pick anything — one row per what? — and de-duplicate on whatever answers that question.

Then check the removed count against what you expected. If you thought there were two accidental repeats and it removed four hundred, stop. That number is the cheapest safety net in the whole job.

Case and whitespace make identical rows look different

These three rows are the same person typed three ways:

• [email protected] , Ana Lee
• [email protected],Ana Lee
• [email protected],Ana Lee

Whole-row de-duplication gives 2 unique row(s); 1 duplicate(s) removed, keeping [email protected],Ana Lee and [email protected],Ana Lee. Two things happened there. Matching ignores case, and spaces around a value are trimmed as the file is read, so the first two rows collapsed even though they look different. But the double space inside Ana Lee is part of the value, so that row survived as distinct. Notice also that the row it kept is printed exactly as you typed it — the lower-casing is used for comparison only, never written into your data.

That is the general shape of the problem. Differences at the edges of a value are handled for you; differences in the middle are not, and neither are Ltd against Ltd., or 0161 496 0000 against 01614960000. Those need fixing before the de-duplication, not by it.

First or last

When two rows share a key, one survives. Keeping the first is right when the earliest record is the authoritative one. Keeping the last is right when later rows are updates — a corrected address, a new phone number — which is the more common case in an export that has been appended to over time. On the order file above, keying on order_id and keeping the last gives 3 unique row(s); 1 duplicate(s) removed (kept the last of each).

The real scope, stated plainly

Name key columns by heading or by 1-based position, comma-separated for more than one; email,item treats a row as a repeat only when both match. If a heading you type does not exist — e-mail when the column is email — it is ignored, and if none of your names resolve the tool falls back to comparing whole rows. That fails in the safe direction, but it means a typo can quietly turn a keyed run into a whole-row run, so read the removed count rather than assuming.

Despite the name, it does not merge anything. It keeps one whole row and drops the other; it never fills a blank in the survivor from a value in the row it discarded. There is no fuzzy or approximate matching. And it reads up to about five thousand rows from a paste, with no warning when it stops — if your file is bigger than that, this is the wrong instrument, and the job belongs in a database or a full spreadsheet application.

Nothing is uploaded. The CSV Merge & De-duplicate tool runs in your browser, which is the only reason it is reasonable to paste a customer export into a web page at all. The method — name the grain, choose the key, check the count — is the part that protects the data.