Arrays · eight-software comparison

UNIQUE distinct region values

Compare distinct-versus-exactly-once handling and array output when deduplicating region values.

Task

Return distinct values from the Region range A2:A6: North appears 3 times; South 2 times.

Expected output

North, South (two distinct values)

Compatibility trap: “Unique” can mean one result per different value, or values that appear exactly once; these are not equivalent.

One shared five-row test dataset

The same source rows make results easier to compare. Use the CSV in any supported spreadsheet application; the ODS workbook also includes LibreOffice formula tests.

Sample data (.csv) ↓Calc test workbook (.ods) ↓

Shared formula comparison fixture (columns A through F)
Region (A)	Status (B)	Amount (C)	SKU (D)	Label (E)	Units (F)
North	Open	125	SKU-101	Alpha	2
South	Closed	80	SKU-102	Beta	1
North	Open	75	SKU-103	Gamma	3
North	Closed	50	SKU-104	Delta	4
South	Open	200	SKU-105	Epsilon	1

Formula syntax and feature differences

“Documented” rows cite official product help, but were not run in the destination software. A formula shown here is a migration candidate, not a guarantee of identical behaviour or file interoperability.

LibreOffice Calc

Native tested

Version: Introduced 24.8.

Arguments: UNIQUE(range;[by_col];[exactly_once])

=UNIQUE(A2:A6)

Native scalar test COUNTA(UNIQUE(...)) returned 2. Visual spill layout was not separately asserted.

Official documentation ↗

Apache OpenOffice Calc

Alternative

Arguments: Data → More Filters → Standard Filter / no duplicates

Use a UI deduplication or helper approach. UNIQUE formula is not claimed from the inspected official function list.

Official documentation ↗

Gnumeric

Documented

Arguments: UNIQUE(data,by_col,exactly_once)

=UNIQUE(A2:A6,FALSE,FALSE)

The Gnumeric manual documents both axes and exactly_once; FALSE for last arg means distinct not singleton-only.

Official documentation ↗

Calligra Sheets

Not researched

No documented UNIQUE worksheet function was established in the reviewed KDE function handbook.

Official documentation ↗

PlanMaker

Not researched

No official UNIQUE function signature was established from the PlanMaker 2026 pages reviewed.

Official documentation ↗

ONLYOFFICE Spreadsheet Editor

Documented

Arguments: UNIQUE(array,[by_col],[exactly_once])

=UNIQUE(A2:A6,FALSE,FALSE)

Set exactly_once FALSE for a deduplicated list of all values, even those repeated.

Official documentation ↗

Apple Numbers

Documented

Arguments: UNIQUE(array,unique-by,occurrence)

=UNIQUE(A2:A6)

Distinct FALSE (default) returns both repeated regions once; occurrence TRUE returns only single-occurrence values.

Official documentation ↗

Zoho Sheet

Documented

Arguments: UNIQUE(range;[col];[occurs_once])

=UNIQUE(A2:A6)

Official Zoho help confirms default FALSE includes all distinct values and TRUE returns only singleton values.

Official documentation ↗

What changes when you migrate?

This fixture purposely has no singleton regions. UNIQUE with exactly_once=TRUE would have a different (potentially empty) result from distinct values.

To test cardinality without relying on dynamic spill placement, the Calc fixture checks COUNTA(UNIQUE(A2:A6))=2.

Do not treat Gnumeric’s published “Subset” classification as absence of the function. Its supported argument behaviour must be read separately.

Actual LibreOffice calculation evidence

Formula tested in LibreOffice Calc 25.2.3.2. The published manifest provides the exact input formula, checked result, and whether the test validates a scalar result rather than array spill layout.

Test ID: unique. See calculation results (JSON).

Distinct versus appears-once

Region in the fixture reads North, South, North, North, South. A distinct list should contain North and South, because it returns one copy of each value no matter how often it appears.

A function option named exactly_once, occurrence or occurs_once can instead request values that appear only once in the entire input. With this sample, neither region qualifies. These two modes have different outcomes and should not be collapsed into one “supported” status.

LibreOffice 24.8 added UNIQUE, and the native workbook checks COUNTA(UNIQUE(A2:A6))=2. Gnumeric’s manual explicitly documents the third argument, while Apple Numbers and Zoho Sheet also document distinct-versus-exactly-once semantics. The native result does not validate the visual spill layout in any other application.

To avoid false positives, also test capitalization, leading spaces and blank cells. Deduplicating spreadsheet records is a data-cleaning operation, and small normalization differences can change which rows survive.