AdvancedData Cleaning
Reconcile a list with hidden spaces
Match two lists where one side has trailing spaces you cannot see.
COUNTIF · IF
12 min35 points
Find which customers are missing from a list
The situation
Harbor Supply's CRM and billing system should hold the same customers. They do not. Before the audit you need to know which CRM customers never made it into billing.
A1
fx
| Row number | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | CRM customer | Name | Status | In billing | ||
| 2 | CUST-402 | Summit Interiors | CUST-118 | |||
| 3 | CUST-118 | Coastal Plumbing | CUST-204 | |||
| 4 | CUST-204 | Ridgeway Hardware | CUST-291 | |||
| 5 | CUST-377 | |||||
| 6 | CUST-500 | |||||
| 7 |
Functions in this exercise
- COUNTIF — Counts cells that meet one condition. Syntax and example
- IF — Returns one value when a test is true and another when it is false. Syntax and example
Who this is for
Recommended next
Related exercises
- 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 - IntermediateData Cleaning
Standardise messy codes with TRIM and UPPER
Clean inconsistent spacing and casing so values can be matched.
7 min25 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