IntermediateData Cleaning
Standardise messy codes with TRIM and UPPER
Clean inconsistent spacing and casing so values can be matched.
7 min25 points
Spot duplicate order numbers with COUNTIF
The situation
An overnight import ran twice and nobody is sure how much of it doubled up. Before anyone starts deleting rows, the duplicated order numbers need marking so the team can check them against the warehouse.
A1
fx
| Row number | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Order | Flag | |||
| 2 | SO-8100 | ||||
| 3 | SO-8101 | ||||
| 4 | SO-8102 | ||||
| 5 | SO-8100 | ||||
| 6 | SO-8103 | ||||
| 7 | SO-8104 | ||||
| 8 | SO-8102 | ||||
| 9 | SO-8105 | ||||
| 10 |
Functions in this exercise
Who this is for
Recommended next
Related exercises
- AdvancedData Cleaning
Find which customers are missing from a list
Use COUNTIF to reconcile two lists and spot what is absent.
COUNTIF · IF
14 min40 points - AdvancedLookup & Reference
Lock ranges so a lookup survives fill-down
Use absolute references so a lookup does not break when copied down a column.
XLOOKUP
12 min40 points - BeginnerLogical Functions
Flag reps below target with IF
Use IF to label each rep as meeting or missing their target.
IF
5 min15 points