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 counts "pune" and "Pune " (with a space) as different – many students see 8 cities instead of 6. Clean the data before using UNIQUE (Module 5). And "exactly_once = TRUE" means items that appear only once, not distinct – this can be asked in an interview.
Ravindra Bagale's Tip – मराठी
UNIQUE "pune" आणि "Pune " (space सोबत) वेगळे मोजतो – बऱ्याच students ना 6 ऐवजी 8 cities दिसतात. UNIQUE लावण्याआधी data clean करा (Module 5). आणि "exactly_once = TRUE" म्हणजे एकदाच आलेले, distinct नाही – हे interview मध्ये विचारलं जाऊ शकतं.
Ravindra Bagale's Tip – हिंदी
UNIQUE "pune" और "Pune " (space के साथ) को अलग गिनता है – बहुत से students को 6 की जगह 8 cities दिखती हैं. UNIQUE लगाने से पहले data clean करो (Module 5). और "exactly_once = TRUE" का मतलब है सिर्फ़ एक बार आने वाले, distinct नहीं – यह interview में पूछा जा सकता है.
Practice task
Build a self-updating city summary with UNIQUE + SUMIFS + COUNTIFS (sales and orders), sorted by sales descending.