UNIQUE function

UNIQUE (Values, ExactlyOnce?) UNIQUE (Values; ExactlyOnce?)

Values

The values to transform.

ExactlyOnce

Logical Logical (optional)

TRUE if no values in the given array should be part of the returned array if they appear multiple times in the given array, FALSE if all values in the given array should be part of the returned array, but only once. If omitted, FALSE is assumed.

Returns

The unique values of the given array.

Returns a version of the given array with all duplicate values removed. UNIQUE ({ 1, 2, 2, 3, 3, 3 }) UNIQUE ({ 1; 2; 2; 3; 3; 3 }) returns { 1, 2, 3 }{ 1; 2; 3 }.

If the ExactlyOnce parameter is TRUE, the returned array does not contain any values which are duplicated in the original array. This formula returns { 1 }{ 1 }, because 2 and 3 are duplicated in the given array:

UNIQUE ({ 1, 2, 2, 3, 3, 3 }, TRUE)

UNIQUE ({ 1; 2; 2; 3; 3; 3 }; TRUE)

Text strings are compared in a case-insensitive manner, and the first spelling is kept. This formula returns { "Ed", "Luke" }{ "Ed"; "Luke" }:

UNIQUE ({ "Ed", "Luke", "ed" })

UNIQUE ({ "Ed"; "Luke"; "ed" })

Removing duplicate rows from tables

If given a table, UNIQUE removes the rows which are duplicates of earlier rows. A row is a duplicate if all its values are equal to the values of the earlier row. This formula returns a table with two rows, one for Ed in Oslo and one for Luke in Rome:

UNIQUE ( TABLE (Name: { "Ed", "Luke", "Ed" }, City: { "Oslo", "Rome", "Oslo" }))

UNIQUE ( TABLE (Name: { "Ed"; "Luke"; "Ed" }; City: { "Oslo"; "Rome"; "Oslo" }))

Examples

UNIQUE ({ 1, 2, 2, 3, 3, 3 })

UNIQUE ({ 1; 2; 2; 3; 3; 3 })

Returns { 1, 2, 3 }{ 1; 2; 3 }, an array in which all values of the given array appear once, in the order they were encountered.

UNIQUE ({ 1, 2, 2, 3, 3, 3 }, TRUE)

UNIQUE ({ 1; 2; 2; 3; 3; 3 }; TRUE)

Returns { 1 }{ 1 }, an array in which only values appear which are not duplicated in the given array.

SORT ( UNIQUE ({ "Sally", "Ed", "Luke", "Ed", "Jenny"}))

SORT ( UNIQUE ({ "Sally"; "Ed"; "Luke"; "Ed"; "Jenny"}))

Returns { "Ed", "Jenny", "Luke", "Sally" }{ "Ed"; "Jenny"; "Luke"; "Sally" }, which is a version of the given array with no duplicates and sorted alphabetically.