Chapter 06 · 8 min · Basic
Which duplicate do you keep
Somebody exported the orders file twice and pasted both exports into one sheet. Now an order id appears more than once, and your total is wrong in a way nobody will spot from the total.
The reflex is to delete the second row. Hold on. Two rows sharing an id come in two kinds, and they need different decisions: rows that agree on everything, which are a copy, and rows that disagree, which are a copy and a correction. Only the first kind is safe to remove without asking anybody.
This one is worth going slowly on, because a duplicate you keep inflates a number and a duplicate you drop deletes a fact.
The grid
Ten order rows in A1:C11 — order_id, the source file each row came from, and amount_myr. Two ids appear twice. Work in columns E and F.
Ten rows, eight orders
Start by measuring the gap rather than hunting for it. COUNTA on the id column counts rows — it returns 10. UNIQUE collapses the column to one row per distinct id, and COUNTA around it counts those instead.
If the two numbers agree, there is nothing to do here. If they do not, the difference is exactly how many extra rows you are carrying.
The sheet has ten rows. How many different order ids do you think are in it?
Scroll to see all 5 columns →
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | order_id | source | amount_myr | ||
| 2 | SO-3001 | export_a | 41.00 | =COUNTA(UNIQUE(A2:A11)) | |
| 3 | SO-3002 | export_a | 62.50 | ||
| 4 | SO-3003 | export_a | 128.75 | ||
| 5 | SO-3001 | export_b | 41.00 | ||
| 6 | SO-3004 | export_a | 33.20 | ||
| 7 | SO-3002 | export_b | 71.15 | ||
| 8 | SO-3005 | export_a | 96.40 | ||
| 9 | SO-3006 | export_a | 27.00 | ||
| 10 | SO-3007 | export_a | 48.00 | ||
| 11 | SO-3008 | export_a | 19.90 |
Basic
The rest of this chapter is Basic
The Excel tutorial and Spreadsheet Fundamentals are free here, forever. This track is the paid half: what to actually do with a spreadsheet once somebody sends you one, from the first look to a summary you can defend.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02Do the two rows agree?
- 03What leaving them costs
- 04The lookup answers with the row you did not choose
- 05Pick a rule, and make it a written one
- 06What the choice breaks downstream