A report by merchant and month is only as good as the merchant names. Bank statements describe the same supplier in several ways, so the first job is a lookup table that maps each messy description to one clean name. After that, a pivot table takes two minutes.
Why does the same supplier show up five ways?
A cafe owner in Brisbane buys beans from a roaster, milk from a dairy, and packaging from a wholesaler. On her statement the roaster appears as "ROASTWORKS PTY 0412", "ROASTWORKS PAYMENT" and "Roastworks Pty Ltd". A pivot treats those as three merchants, and her report understates every one of them.
How do I build the lookup table?
On a second tab, make two columns. Column H holds the messy text exactly as it appears, one variant per row. Column I holds the clean name you want.
| Messy description (H) | Clean merchant (I) |
|---|---|
| ROASTWORKS PTY 0412 | Roastworks |
| ROASTWORKS PAYMENT | Roastworks |
| Roastworks Pty Ltd | Roastworks |
| COUNTRY DAIRY CO | Country Dairy |
| PACKWELL WHSL | Packwell |
Then in the data tab, with raw descriptions in column B, add a clean column E with =IFERROR(VLOOKUP(TRIM(B2), $H$2:$I$60, 2, FALSE), TRIM(B2)). The 2 returns the clean name, FALSE demands an exact match, and IFERROR falls back to the original text if no match is found, so unknown merchants stay visible instead of vanishing.
How do I find the descriptions I have not mapped?
Filter column E for rows whose value still equals column B. Those are the unmapped ones. Add each to the lookup table, and the formula picks them up on its own. In a month or two the unmapped list shrinks to new suppliers only.
What is the monthly report?
- Add a Month column: =TEXT(A2,"yyyy-mm") if dates are in column A.
- Select the data and insert a pivot table.
- Put the clean merchant column in Rows, Month in Columns and Amount in Values as a Sum.
- Check the grand total against the raw amount total.
Suppose the result for three months is Roastworks A$840.00, A$912.50 and A$788.25; Country Dairy A$296.40, A$301.80 and A$289.10; Packwell A$150.00, A$0.00 and A$215.65. The roaster's three-month total is 840.00 + 912.50 + 788.25 = A$2,540.75. The dairy's is 296.40 + 301.80 + 289.10 = A$887.30, and the packaging total is 150.00 + 0.00 + 215.65 = A$365.65. Together they come to 2,540.75 + 887.30 + 365.65 = A$3,793.70. All invented.
What about merchants that really are different?
Be careful with broad rules. Mapping everything containing "PACK" to Packwell would also swallow a different packaging firm. Prefer exact descriptions in the table over clever wildcards, even if it means a longer list at first.
Where does an app fit?
Some tools learn from corrections. SpendixAI normalises merchant names, lets you add aliases and turns your corrections into history, and its report builder can group by merchant, save the report and export CSV. It is built around India and INR and we do not claim it handles Australian statements; use it as a yardstick for features.
Spotting a supplier whose costs are creeping
Once names are clean, the monthly view is a quick early-warning tool. Compare each merchant's latest month with the average of the earlier ones. Roastworks averaged (840.00 + 912.50) / 2 = A$876.25 across the first two months and then spent A$788.25, a fall of A$88.00; a rise would be the thing to question. Ask whether it reflects volume, a price change or a duplicate payment. Look first at the dates of the individual rows behind the number, because a single large order explains many surprises.
Quarterly routine
- Export the statements and paste them below the old data.
- Check for unmapped merchants and add them to the table.
- Refresh the pivot and compare the grand total with the raw total.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

