In Google Sheets, =UNIQUE(C2:C500) returns each category once. Use FILTER inside it to leave out blank cells, SORT around it for an A to Z list, and run it on the merchant column to find the same shop typed in several ways. Spelling variants stand out when the sorted list puts them next to each other.
What is the basic UNIQUE formula?
The syntax is =UNIQUE(range, [by_column], [exactly_once]). Typed in one cell, it spills a list below. Sheets spills automatically, so make sure the cells underneath are empty.
- =UNIQUE(C2:C500): distinct categories in order of first appearance
- =UNIQUE(B2:B500): distinct merchant names
- =UNIQUE(B2:C500): distinct merchant and category pairs in two columns
- =UNIQUE(C2:C500,FALSE,TRUE): categories used only once, useful for spotting one-off labels
How do I leave out blank cells?
If the range includes empty rows at the bottom, the list ends with a blank entry. Filter first, then take the unique values:
=SORT(UNIQUE(FILTER(B2:B500,B2:B500<>"")))
The order matters. FILTER removes empty cells, UNIQUE removes repeats and SORT arranges the rest. If every cell in the range is blank, FILTER returns an error, so wrap the formula in IFNA when the sheet is new.
How can a unique list reveal spelling variants?
This is the most useful trick with merchant names. Sort the unique merchant list and read down it. A hypothetical output after a few months of statements might look like this:
| Unique merchant | Probably the same as | Action |
|---|---|---|
| Dmart | D Mart | Pick one name |
| D Mart | Dmart | Pick one name |
| Swiggy | Swiggy Instamart? | Decide if they are separate |
| Swiggy Instamart | Swiggy? | Decide if they are separate |
| Ramesh Kirana | Ramesh Kirana | Trailing space; fix with TRIM |
Once you have decided, add a column beside the merchant data that holds the tidy name, and use it in every lookup and total. The aim is that each real-world shop appears once.
How many times does each one appear?
With the unique list in E2 downwards, count the matches next to it:
=ARRAYFORMULA(IF(E2:E="","",COUNTIF($B$2:$B$500,E2:E)))
And the spend per merchant, with amounts in column D:
=ARRAYFORMULA(IF(E2:E="","",SUMIF($B$2:$B$500,E2:E,$D$2:$D$500)))
Sort the result by the count or amount with SORT, or view it in descending order using =SORT(E2:G,3,FALSE) on a three-column block. The merchants you hit most often are the ones worth giving a rule.
Should the list update itself when I add rows?
Use ranges that run beyond your last row, such as B2:B1000, or open-ended ones such as B2:B. An open-ended range keeps working as months of statements are pasted below, with no editing. The cost is that FILTER is needed to drop blank cells, which is why the pattern above always starts with it.
Remember that the unique list is a snapshot of names, not a rule. Seeing Dmart and D Mart side by side only helps once you do something about it, such as a tidy-name column or a rule in your categorising formula.
What if two categories share one merchant?
Run =UNIQUE(B2:C500) and sort by the first column. If one merchant appears twice with different categories, say a big retailer filed under both Groceries and Household, your rule is inconsistent. Decide which category should win, or split the merchant by amount range in your rules.
Short checklist
- Filter blanks before UNIQUE
- Sort the merchant list and read it top to bottom once a month
- Create a tidy-name column rather than editing the raw statement
- Count each merchant, then add rules for the busiest ones
- Review one-offs with the exactly-once option
If tidying merchant names becomes a regular job, SpendixAI lets you add merchant aliases and remembers your corrections, so the next statement import starts from the cleaned names.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


