Collabora Office UNIQUE: Distinct Records and Exactly-Once Results
Deduplicate complete rows or columns, preserve first occurrences and understand string-key comparison and exactly-once filtering. Scoped to the new Linux desktop source build.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
UNIQUE returns distinct records from rows or columns. Its default keeps the first occurrence of each distinct record; its exactly-once mode instead removes every record whose comparison key appears more than once. These are different filtering goals.
Syntax and comparison direction
=UNIQUE(Array [; By_col [; Uniqueness]])
By_col defaults to FALSE, comparing complete rows. TRUE compares complete columns. Uniqueness defaults to FALSE, returning one representative of each distinct key. TRUE returns only keys that occur exactly once.
The checked source constructs comparison keys from each cell’s string representation, separated by a control delimiter, then lowercases the combined key. It copies the original first record into the result. Consequently, this is not a guaranteed type-sensitive database-key operation. Do not assume numeric 1 and text “1” must remain distinct, or that arbitrary embedded control characters cannot collide in a constructed key.
Step by step: distinct pairs
Enter A2:B6 as:
2 20
1 10
2 20
3 30
1 11
For =UNIQUE(A2:B6), the independent/source-derived output is:
2 20
1 10
3 30
1 11
The repeated [2,20] row is represented once. The two rows beginning with 1 are different because their second values differ. Comparing only column A would answer a different question.
The result preserves the order of first occurrences in the source’s scan; it is not automatically sorted. The sequence starts with 2 even though 1 is numerically smaller.
Exactly once is not ordinary deduplication
=UNIQUE(A2:B6;FALSE;TRUE)
This removes both occurrences of [2,20]. The expected remaining rows are [1,10], [3,30] and [1,11], in their original relative order.
On the single column A2:A6, =UNIQUE(A2:A6) returns 2,1,3 as a column. =UNIQUE(A2:A6;FALSE;TRUE) returns only 3, because both 1 and 2 occur twice. Use the full intended record range when the uniqueness decision depends on multiple fields.
Compare columns instead
Enter D2:H3 as the transposed record layout:
2 1 2 3 1
20 10 20 30 11
=UNIQUE(D2:H3;TRUE) compares complete columns and returns:
2 1 3 1
20 10 30 11
The result has two rows and four columns. By_col changes the record orientation; it does not mean “compare each individual cell independently.”
ASCII case-insensitivity and original output
Put North, north and SOUTH in J2:J4. The source-derived result of =UNIQUE(J2:J4) is North, SOUTH. The first two strings share a lowercase comparison key, but the output retains the first original spelling, North.
=UNIQUE(J2:J4;FALSE;TRUE) retains only SOUTH. These ASCII examples do not establish every locale’s case-folding, accent or normalization behavior. If capitalization itself distinguishes records in a workflow, this implementation is not a case-sensitive uniqueness test.
Advanced: deduplicate the right record
A two-field record such as [Product,Location] should normally be compared as a pair if both fields define identity. Deduplicating each field separately and placing the two results beside one another can create combinations that never occurred in the original data.
Conversely, including a transaction-specific field such as a timestamp can prevent otherwise similar records from matching. Decide which fields define the question before choosing the range. UNIQUE does not infer business identity, correct misspellings or resolve conflicting values in duplicate records.
The source compares string keys and then copies retained original cells. Formatting, blank cells, empty text, errors and mixed types require their own validation before being used as critical keys. This guide’s complete controls use ordinary integers or simple ASCII text and do not certify arbitrary-key collision safety.
Empty results and UI scope
With L2:L3 both equal to 7, =UNIQUE(L2:L3;FALSE;TRUE) has no surviving record. The source returns internal NotAvailable, rather than a zero-length array. Exact localized display was not observed.
The help describes array entry and a Ctrl+Shift+Enter hint. This guide verifies source comparison logic and independent matrix layouts, not that shortcut, spill, resizing or worksheet placement in the new Qt desktop. Result dimensions depend on surviving records, so do not assume a fixed output count merely from the source range size.
Product scope and evidence
This guide concerns the new Collabora Office desktop for Linux, source tag coda-26.04.4.3-1, commit 81339d176148d0d5c81ff05d82540f58f8aaa117. The 26.04 UNIQUE help and exact evaluator source were checked October 18, 2026. The Linux package manifest supplies the recorded product mapping; its mutable publication is not an installed-binary attestation.
No Collabora runtime or spreadsheet calculation engine was executed. Examples are source-derived expectations and independent mathematical controls. No screenshots or interface tests are claimed. This evidence does not establish Classic, Online, CODE, mobile, Windows or macOS parity.
The first Collabora release introducing this function is unknown from the checked evidence. Current documentation and registration do not establish retirement or deprecation, and upstream release history is not substituted for product-specific introduction. Import/export behavior in other products was not tested.