Duplicates — mark, remove, combine
Three different needs that all sound like "deduplicate": flag the repeated rows, produce a list with no repeats, or collapse several rows for one customer into one with the numbers added up. Plus how this differs from a primary key.
Which of the three do you want?
| What you want | Use |
|---|---|
| To see which rows are repeated | Tools → Find duplicate rows → Mark Duplicate Rows |
| A list with no repeats in it | Tools → Find duplicate rows → Remove duplicates |
| One customer appearing on several rows collapsed into one, with the order numbers joined and the amounts added | Tools → Combine-duplicates |
Find duplicate rows
Tools → Find duplicate rows:
- Choose or upload the table to search.
- Choose the columns to search — which columns decide "the same". Just the phone number, and rows sharing a phone number are duplicates; name and phone number, and both have to match.
- Pick the type: Mark Duplicate Rows (adds a marker column, every row kept) or Remove duplicates (a new table with only the unique rows). Advanced Options has a Match case switch.
- OK.
Marking suits looking before deciding; removing suits going straight to the answer. Both results are live — new rows in the source are judged again.
Combine-duplicates
Tools → Combine-duplicates:
- Choose or upload the table.
- Choose the columns that identify a duplicate — what makes two rows "the same thing", e.g. Customer.
- Choose what to do with the other columns:
- Combine the values into one, with a separator — comma, space, semicolon or newline. An order-number column combined reads
A001, A007, A012. - Calculate a value: sum, count, average, min, max. The amount column set to Sum gives each customer's total. Each column is configured separately; Add a column puts the result in a new column instead of overwriting. Advanced Options has Match case here too.
- Combine the values into one, with a separator — comma, space, semicolon or newline. An order-number column combined reads
- Run.
The result is a new table converged on Customer: one row per customer, order numbers joined, amounts totalled. It is really a flavour of Summarize, approached from the "duplicates" angle.
Against a primary key
Setting a primary key also deduplicates, but it does so as data arrives, keeping only the latest row per key. That solves "the same record got imported twice". The tools on this page solve "the data genuinely contains several similar records", as an analysis question. The first changes what the table stores; the second only derives a new table or column.