Removing Duplicates

The till lost its connection for ten minutes on 3 June and re-sent its orders when it came back. Now some orders appear twice, and June's revenue looks better than it really was. Duplicates inflate every total and count, but deleting the wrong row is just as bad. The skill is deciding what "the same" means.

The rule

Decide what makes a row unique, then keep one per key.

There are two kinds of duplicate:

• Exact duplicates: every column is identical, including the ID. These are almost always copying errors, like the till re-sending.
• Near duplicates: the same real event recorded with small differences, such as a different ID, a trailing space, or Asha Rao versus asha rao.

And one important non-duplicate: two genuine events that look alike. Asha buying two croissants on Monday and again on Wednesday is two orders, even if the item, quantity and amount match.

The fix is to choose the key: the columns that together identify one real event. For orders, that is normally order_id. For a customer list without IDs, it might be name plus phone number, after cleaning both.

The two 1004 rows share a key: keep one. Orders 1005 and 1008 have different IDs and dates: both are real.

In Excel, Data → Remove Duplicates lets you tick which columns form the key. In SQL, SELECT DISTINCT or GROUP BY the key does the same job.

The shortcut

Before removing anything, count rows per key. Any key with a count above 1 is a candidate. Then clean the key first (TRIM, fix case), because "Asha Rao " and "Asha Rao" will not be seen as the same.

The trap: a key that is too loose. De-duplicating on customer, item and amount alone would delete Dev's second coffee, a genuine repeat order.

Drills

Answer fast — trust the rule.

Remember

• Decide what makes a row unique, then keep one per key.
• Shortcut: count rows per key, clean the key, inspect before deleting.
• Trap: a key that is too loose deletes genuine repeat orders.