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?
| Function | Job in this formula | Without it |
|---|---|---|
| FILTER | Keeps only merchants in this category | You would join every merchant in the sheet |
| UNIQUE | Collapses repeat visits to one name | DMart appears once per bill, 40 times |
| TEXTJOIN | Joins with a separator | You get a spilled column, not one cell |
| The empty text at the end of FILTER | Returns blank when nothing matches | The 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.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


