Dynamic arrays
UNIQUE function: deduplicate a list vs find values that appear only once
People use the word "unique" for two different tasks: listing every name once (distinct) and isolating names that never repeated (exactly once). Control both behaviours with UNIQUE and handle empty results safely.
Six workshop check-ins, four attendees
Suppose six session check-ins record Klara twice, Mateo twice, Sonia once, and Devon once in A2:A7. Before writing a formula, decide whether repeat visitors should appear once on your badge list or be excluded completely.
| Check-in row | Attendee (A) |
|---|---|
| Row 2 | Klara |
| Row 3 | Mateo |
| Row 4 | Klara |
| Row 5 | Sonia |
| Row 6 | Mateo |
| Row 7 | Devon |
Default UNIQUE keeps one copy of every distinct value
With a single range argument, =UNIQUE(A2:A7) returns each attendee once in order of first appearance: Klara, Mateo, Sonia, Devon. Wrapping it in COUNTA counts 4 distinct attendees, and wrapping it in TEXTJOIN displays the full deduplicated list in a single cell.
=TEXTJOIN(", ",TRUE,UNIQUE(A2:A7))Set the third argument (exactly_once) to TRUE for single-visit names
UNIQUE’s second argument (by_col) is FALSE because our data runs down a column. Setting the third argument (exactly_once) to TRUE drops anyone who checked in more than once—removing Klara and Mateo altogether and returning only Sonia and Devon.
=TEXTJOIN(", ",TRUE,UNIQUE(A2:A7,FALSE,TRUE))Watch for #CALC! when every item repeats
If every attendee in A2:A7 checked in at least twice, UNIQUE(A2:A7, FALSE, TRUE) has zero items to return and produces #CALC!. Unlike FILTER, UNIQUE has no built-in if_empty parameter, so wrap occurs-once checks in IFERROR when repeats across the entire list are possible.
=IFERROR(TEXTJOIN(", ",TRUE,UNIQUE(A2:A7,FALSE,TRUE)),"Every attendee repeated")Test the difference in the practice grid
Change A5 from Sonia to Klara and A7 from Devon to Mateo so only repeat visitors remain. Distinct COUNTA(UNIQUE(A2:A7)) becomes 2 (Klara and Mateo), while raw UNIQUE(A2:A7, FALSE, TRUE) returns #CALC! and the guarded formula reports "Every attendee repeated".
Try the example
Use the original values or change them to test your understanding.