Spreadsheets

UNIQUE in Google Sheets: list distinct categories and merchants

Use UNIQUE in Google Sheets to list distinct categories and merchants, skip blanks with FILTER and spot spelling variants of one merchant by sorting.

Editorial illustration for UNIQUE in Google Sheets: list distinct categories and merchants

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 merchantProbably the same asAction
DmartD MartPick one name
D MartDmartPick one name
SwiggySwiggy Instamart?Decide if they are separate
Swiggy InstamartSwiggy?Decide if they are separate
Ramesh KiranaRamesh KiranaTrailing 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.

SPENDIXAI JOURNAL

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

Explore your reports