9.4 UNIQUE
=UNIQUE(array, [by_col], [exactly_once])
| Need | Formula | Result (mini data) |
|---|---|---|
| Distinct cities | =UNIQUE(tblMini[City]) |
6 cities |
| Distinct cities sorted | =SORT(UNIQUE(tblMini[City])) |
Kolhapur … Solapur |
| Count distinct | =COUNTA(UNIQUE(tblMini[City])) |
6 |
| Cities appearing exactly once | =UNIQUE(tblMini[City], , TRUE) |
Nagpur, Kolhapur, Solapur, Sambhaji Nagar |
| Distinct City–Platform pairs | =UNIQUE(tblMini[[City]:[Platform]]) |
7 pairs |
Worked example – a self-updating summary.
=LET(c, SORT(UNIQUE(tblMini[City])),
HSTACK(c, SUMIFS(tblMini[Amount], tblMini[City], c)))
| City | Sales |
|---|---|
| Kolhapur | 232 |
| Nagpur | 240 |
| Nashik | 360 |
| Pune | 544 |
| Sambhaji Nagar | 195 |
| Solapur | 1,299 |
Ravindra Bagale's Tip
UNIQUE "pune" aani "Pune " (space sobat) vegle mojto – khup students la 6 aivaji 8 cities disatat. UNIQUE lavaychya aadhi data clean kara (Module 5). Ani "exactly_once = TRUE" mhanje ekdach aalele, distinct nahi – he interview madhe vicharla jaau shakta.
Practice task
Build a self-updating city summary with UNIQUE + SUMIFS + COUNTIFS (sales and orders), sorted by sales descending.