Spreadsheets

List merchants per category in one cell with TEXTJOIN and FILTER

List every merchant in each spending category in one cell using TEXTJOIN, FILTER and UNIQUE, with no pivot table and results that update with your data.

Editorial illustration for List merchants per category in one cell with TEXTJOIN and FILTER

To show all merchants of one category in a single cell, use =TEXTJOIN(", ",TRUE,UNIQUE(FILTER($C$2:$C$500,$B$2:$B$500=E2,""))). FILTER picks the merchants whose category equals the one in E2, UNIQUE removes repeats, and TEXTJOIN glues them into one comma-separated line such as "BigBasket, DMart, Zepto". It needs Excel 365 or 2021, or Google Sheets.

What layout does this formula expect?

Say your transaction sheet has dates in A, categories in B, merchants in C and amounts in D, from row 2 to row 500. On a summary sheet or to the right, build two columns:

  • E2: =SORT(UNIQUE(FILTER($B$2:$B$500,$B$2:$B$500<>""))) spills a clean A-to-Z category list.
  • F2: =TEXTJOIN(", ",TRUE,UNIQUE(FILTER($C$2:$C$500,$B$2:$B$500=E2,""))) joins the merchants for the category in E2. Copy it down next to the spilled list.
  • G2: =COUNTIFS($B$2:$B$500,E2) shows how many transactions each category holds.

The result reads like a quick audit sheet: Groceries on one line with every shop you used, Travel on the next with every cab or ticket merchant.

Why is each wrapper there?

FunctionJob in this formulaWithout it
FILTERKeeps only merchants in this categoryYou would join every merchant in the sheet
UNIQUECollapses repeat visits to one nameDMart appears once per bill, 40 times
TEXTJOINJoins with a separatorYou get a spilled column, not one cell
The empty text at the end of FILTERReturns blank when nothing matchesThe cell shows #CALC! for an empty category

The TRUE in the middle tells TEXTJOIN to ignore empty cells, so a blank merchant on a cash row does not leave ", ," in your text.

Can one formula do the whole column?

In recent Microsoft 365, BYROW with LAMBDA does it from the spilled list alone:

  • F2: =BYROW(E2#,LAMBDA(cat,TEXTJOIN(", ",TRUE,UNIQUE(FILTER($C$2:$C$500,$B$2:$B$500=cat,"")))))

E2# means the whole spilled category list, and cat stands for each category in turn. If BYROW is not recognised, you are on an older build, so copy the single-cell version down instead. In Google Sheets, TEXTJOIN, FILTER and UNIQUE work the same way; wrap the per-row version in ARRAYFORMULA if you want one formula.

How do I keep the merchant names tidy?

UNIQUE treats "Swiggy" and "SWIGGY " as different names, and statements often print the same shop in several ways. Wrap the merchant column in TRIM and PROPER inside UNIQUE, for example UNIQUE(PROPER(TRIM(FILTER(...)))), so the list reads cleanly. This tidies display only; it does not change the raw rows.

Here is a made-up example. Suppose Groceries holds 31 transactions across BigBasket, DMart, Zepto and a local kirana typed as Cash. The F cell reads BigBasket, DMart, Zepto, Cash and the G cell reads 31. If you notice that Zepto also appears next to Eating out, you have found either a mis-tagged row or a merchant you use for two purposes. Both are worth a look, and both took one glance instead of a pivot table and a filter.

What do I do with the result?

  • Scan for a merchant sitting in the wrong category: "Zomato" listed under Groceries will jump out in one line.
  • Check for duplicates in spirit: "Amazon" under both Shopping and Bills means one of them is wrong.
  • Use it as a mapping reference when you build a lookup list for auto-categorising next month's rows.
  • Hide the merchants column in print and keep only the category and total.
  • Sort the summary by the count in column G to see which categories carry most of your activity.

A sheet like this is a good checking tool, but it only shows what you already categorised. SpendixAI categorises imported rows through your own rules, then your history, and then AI only if you switch it on, and keeps corrections as history for next time.

SPENDIXAI JOURNAL

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

Explore your reports