UNIQUE
Return each distinct value or row from a range once, in the order first seen. Spills into as many cells as there are distinct values.
Syntax
UNIQUE(array)| Argument | Required | Description |
|---|---|---|
array | Required | The range to take distinct values or rows from. |
Examples
Every example below was executed against VisiGrid engine 0.51.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=C1 | East | UNIQUE(A2:A6) is in C1 and spills down. |
=C2 | West | |
=C3 | North | "east" counts as the same as "East", as in Excel, so the third distinct value is North. |
=COUNTA(C1:C5) | 3 | Three distinct regions. |
First-seen order
Values come out in the order they first appear, not sorted. For an
alphabetical list, wrap it in SORT:
=SORT(UNIQUE(A2:A6))UNIQUE also takes a computed list, so UNIQUE(FILTER(...)) gives the
distinct values among the rows that meet a condition.
Case is ignored
“East” and “east” are one value, and the first spelling seen is the one kept.
That matches Excel. If case matters in your data, clean it first with
UPPER or PROPER in a helper
column.
Whole rows
Given more than one column, UNIQUE compares whole rows: two rows are
duplicates only if every column matches.
Not yet supported
Excel’s UNIQUE takes two more arguments — by_col, to compare columns instead
of rows, and exactly_once, to return only values that appear a single time.
Only the one-argument form is available here; passing more returns an error.
Excel compatibility
Matches Excel's one-argument form, including comparing text without regard to case. Excel's optional by_col and exactly_once arguments are not supported yet.