The UNIQUE function is a related function of Spill added in 2019 that removes duplicates in a cell range.It is also possible to retrieve only duplicate data.
How it works
=UNIQUE(array,by_col,exactly_once)
| Name | Omission | Explanation |
|---|---|---|
| array | Required argument. Cell range from which to remove duplicates. | |
| by_col | FALSE | Direction of duplicate deletion. TRUE: Column direction. FALSE: Row direction. |
| exactly_once | FALSE | Data to be obtained TRUE: Unique data only FALSE: Delete duplicate data |
Demonstrate
Argument 1: array
Enter a formula and the result will be displayed in the cell range specified in Argument 1.
The result is displayed with duplicates removed in the row direction.
The formula is automatically expanded to cells other than the cell where the formula was entered, but these cells are ghost cells and cannot be edited.
Argument 2: by_col
Specifying TRUE for this argument changes the direction of duplicate removal from vertical to horizontal.
If this argument is omitted, the direction is vertical.
Argument 3: exactly_once
If TRUE is specified for this argument, only non-duplicated data will be displayed.
If all are duplicates, a #CALC will result
If this argument is omitted or FALSE is specified, duplicates are removed and retrieved.
Multiple cell ranges.
Multiple cell ranges can be targeted with the VSTACK and HSTACK functions.
---