cleaning data

Duplicate rows

Find out how many rows repeat another, whole or by one column, exactly or ignoring capitals and spaces, and remove them keeping the first.

Rows › Remove Duplicate Rows… doesn’t remove anything until you choose what a duplicate is. It counts first, and shows four choices with how many rows each would remove:

Choice Two rows are duplicates when…
Identical rows every value is the same
Identical rows, ignoring case and spaces every value is the same once capitals, spaces at the edges and repeated spaces between words are set aside
Same “email” the cursor’s column has the same value (here, email), whatever the others hold
Same “email”, ignoring case and spaces the same, in that column only, setting aside capitals and spaces

Pick one and press Enter: of each group of duplicates, the first row is kept and the others are removed. The message at the bottom says how many went, and Ctrl+Z⌘Z brings them all back in one step.

The rows are compared whole, values with line breaks included, and the header row is never one of them. Select two rows or more first to look for duplicates among those only.

Keeping the right row

The row that stays is the one that comes first. To keep the most recent of each customer instead, say, sort by date in descending order first (see Sorting rows), and then remove the rows with the same customer.

If the counts in the list are different from what you expected, the “ignoring case and spaces” choices usually tell you why: values that look the same but have a space at the end, or a capital letter. Whitespace and case fixes those for good.