Chapter 05 · 8 min · Basic
The same branch, four ways
Somebody typed the branch name by hand, ten times, over three months. You now have Penang, penang, PENANG and Penang — and as far as the sheet is concerned that last one, with a space on the end, is a different place.
The instinct is to start fixing. Don't. Enumerate first: list every spelling that exists, work out which ones your formulas already cope with, fix the mechanical half, and leave a written mapping for the half only a human can judge.
This chapter also answers a question worth knowing for the rest of your career: which of these differences does Excel quietly forgive, and which does it not?
The grid
Ten transactions in A1:B11 — branch as somebody typed it, and amount_myr. Beside them in D1:E4 is a small mapping table: how a name was written, and what it should be. It is three rows long on purpose. Work in columns G and H.
List the spellings before fixing any
UNIQUE on the branch column gives every distinct value, one row each. That list is the real inventory of the problem, and it fits on a screen — which a column of ten thousand rows never will.
Run it and count what comes back. Then read the list carefully: two of those rows look exactly the same.
Look down column A and count the spellings you can see. How many rows will UNIQUE return?
Scroll to see all 7 columns →
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | branch | amount_myr | as_written | should_be | |||
| 2 | Penang | 41.00 | KL | Kuala Lumpur | =UNIQUE(A2:A11) | ||
| 3 | KL | 62.50 | penang | Penang | |||
| 4 | penang | 54.30 | PENANG | Penang | |||
| 5 | Kuala Lumpur | 128.75 | |||||
| 6 | Penang | 33.20 | |||||
| 7 | KL | 96.40 | |||||
| 8 | PENANG | 27.00 | |||||
| 9 | Kuala Lumpur | 71.15 | |||||
| 10 | Johor Bahru | 48.00 | |||||
| 11 | penang | 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
- 02Case is forgiven, spacing is not
- 03The difference you cannot see
- 04Fix the mechanical half
- 05And what the mapping does not cover
- 06Stop at coverage, not perfection