The quickest way through a pile of unmatched merchants is to stop reading rows and start grouping them. Pull out a short key from each description, count how often each key appears, total the pounds against it, and categorise the keys rather than the individual rows.
UK statement lines often carry a store number, a town and sometimes a payment-method tag, so the same shop appears as four or five separate entries. Grouping by a key collapses them before you do any real thinking.
How do you make a key from a messy description?
In a spreadsheet with raw descriptions in column A from A2 down, put this in B2 and fill down: =LEFT(A2, FIND(" ", A2&" ") - 1). It returns the first word, and the extra space inside FIND stops it failing on one-word descriptions. When the first word is generic, such as a payment tag, build the key from the second word instead, and use these to count and total:
- Second word: =TRIM(MID(A2, FIND(" ", A2&" ") + 1, 20)) gives a rough second chunk to inspect.
- Count per key: =COUNTIF($B$2:$B$500, B2).
- Pounds per key: =SUMIF($B$2:$B$500, B2, $C$2:$C$500) with amounts in column C.
Keys are a shortcut, not a verdict. Skim each one and make sure it really is one shop before you categorise it.
What does the ranked list look like?
Suppose a quarter leaves 156 unmatched rows. After keying, they collapse into 38 keys. Sorted by pounds, the top of the list might look like this.
| Key | Rows | Total | Category you assign |
|---|---|---|---|
| GREENFIELD | 34 | £412.60 | Groceries |
| RAILCARD | 9 | £287.10 | Travel |
| KIOSK | 27 | £96.40 | Coffee and snacks |
| PHARMA | 6 | £58.20 | Health |
Those four keys cover 76 rows and £854.30 (412.60 + 287.10 + 96.40 + 58.20). That is nearly half the rows from four decisions.
Can one key hide two different shops?
Yes, and this is the main risk. A key such as CITY could be a bike hire scheme and a cafe. Whenever a key's rows span a wide spread of amounts, sort the rows by amount and read the top and bottom. If a £2.80 row and a £64 row share a key, they probably do not belong together.
What do you do with the 20 or so keys that are left?
- Sort by pounds again and take the next ten. Categorise them the same way.
- For the rest, check whether any is a person's name or a faster-payment reference. Those are transfers, not shops, so give them a Transfers or People category.
- Leave genuine one-offs alone and note how many are left.
- Save your key-to-category table on its own tab so next month starts from it.
Keeping it fixed from month to month
Paste next month's rows below the old ones, rebuild the keys with the same formula and look up your saved table with =IFERROR(VLOOKUP(B2, $G$2:$H$60, 2, FALSE), "Unmapped"). Whatever comes back as Unmapped is the only list you need to work on. In most months that is a handful of rows.
Before you close the file
- Note rows and pounds unmatched before and after the pass.
- Check that totals by category still add up to your total spend, to catch accidental deletions.
- Keep the mapping tab; it is the real asset, not the tidy month.
- Book the next pass in your diary for the end of the following month.
Nothing here depends on a particular bank or app, so the method carries over if you change either.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

