Expense categories

Fix unmatched merchants in one pass: a method for US card statements

A step-by-step way to list unmatched merchants from US card statements, rank them by dollars, and fix the biggest ones in a single sitting.

Editorial illustration for Fix unmatched merchants in one pass: a method for US card statements

To fix unmatched merchants in one pass, list every raw merchant name that has no category, sort the list by how many dollars it represents, and assign categories from the top down until what is left is too small to matter. Most of the benefit sits in the first dozen names, so you rarely need to touch every row.

A card statement is full of names no person chose: payment-processor prefixes, store numbers, city codes and reference strings glued to the end. Your tracker treats each variant as a separate merchant, which is why the unmatched list looks endless even when you only visit a handful of places.

Build the list before you fix anything

Export your statement rows to a spreadsheet, or paste them in, and keep the raw description in column A and the amount in column B. Put the unique raw names in their own column first (copy, then Data, Remove duplicates) and add two helper formulas beside each unique name:

  • Rows: =COUNTIF($A$2:$A$600, E2) where E2 is the unique name and column A holds every raw description.
  • Dollars: =SUMIF($A$2:$A$600, E2, $B$2:$B$600) to add up the amounts for that name.
  • Then sort the unique list by the dollars column, largest first.

Which names deserve attention first?

Say three months of card activity left 214 uncategorised rows spread over 61 distinct raw names, adding up to $6,480. Group them by size and the picture becomes clear.

BandDistinct namesRowsSpend
Top 12 by spend12138$4,610
Next 20 by spend2052$1,370
Remaining one-offs2924$500
Total61214$6,480

Fixing only the top 12 names clears 138 of the 214 rows (about 65 percent) and $4,610 of the $6,480 (about 71 percent). That is roughly a quarter of an hour of work for most of the value.

How do you map several raw names to one merchant?

  1. Choose the clean name you want to see in reports, for example Harbor Coffee.
  2. Collect every variant that starts with the same stem: SQ *HARBOR COFFEE 0412, SQ *HARBOR COFFEE 0977 and HARBOR COFFEE CO NYC all point to one place.
  3. Write one rule that says the description contains HARBOR COFFEE, then give it a category such as Dining out.
  4. Run the rule over last month as a test. If a row lands wrongly, tighten the stem rather than adding exceptions.
  5. Move to the next name on your ranked list.

What if one merchant fits two categories?

A big-box store sells groceries one day and a monitor the next. Resist writing a rule per item. Give the merchant the category that covers most of your visits, say Groceries, and correct the odd purchase by hand. If you find yourself correcting more than a third of its rows, switch the default and correct the other way.

Does the fix hold next month?

It holds only if the rules or aliases are saved where your tool can reuse them. In a spreadsheet, keep a mapping tab with two columns, raw stem and clean name, and look it up with =XLOOKUP(LEFT(A2,12), $H$2:$H$80, $I$2:$I$80, "Unmapped") in Excel 365, Excel 2021 or Google Sheets. New months will mostly resolve themselves, and the Unmapped rows become your short list for the next sitting.

A quick checklist for the sitting

  • Count unmatched rows and unmatched dollars before you start, and write both down.
  • Work from the biggest dollar total down, not from the top of the statement.
  • Name each merchant once, then categorise it once.
  • Stop when the remaining names are each below your own small-amount threshold.
  • Count again and note how many rows and dollars you cleared.
  • Repeat the pass monthly; the second one usually takes a fraction of the time.

Rules about what counts as a business or household expense differ by situation, so keep any tax-related labelling for a conversation with your own accountant.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports