UNIQUE

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

Returns the array of unique values in a list, table or cell range.

array	The range or array from which to return unique values.
by_col	(Optional) A logical value indicating whether to compare rows or columns: False (or 0) = Comparing Rows, data is arranged from top to bottom (default) True (<> 0) = Comparing Columns, data is arranged from left to right
exactly_once	(Optional) A logical value indicating which values to return: False (or 0) Unique values (default) True (<> 0) Unique values that only occur once

REMARKS

* This function was added in Excel 2021.
* This function can create a Dynamic Array Formula.
* If "by_col" is left blank, then False is used.
* If "by_col" = False and the data is arranged in a column, then unique items will be returned.
* If "by_col" = True and the data is arranged in a row, then unique items will be returned.
* If "by_col" = True and the data is arranged in a column (instead of a row), then every item is returned. See Example 4.
* If "exactly_once" is left blank, then False is used.
* If "exactly_once" = False, then all the unique values will be returned.
* If "exactly_once" = True, then only the unique values that occur once will be returned.
* If there are no unique rows (or columns) and "exactly_once" = True, Then #CALC! is returned.
* If there are not enough blank cells to display the full array, then #SPILL! is returned. Example 10.
* You can use the DUPLICATEVALUES - User Defined Function function to return a list of items that appear more than once.
* You can use the FILTER function to remove any blank rows or columns.
* You can use the SORT function to return an array of data that has been sorted.
* You can use the SORTBY function to sort data based on multiple columns or values in a range.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

	A	B	C	D
1	=UNIQUE(B1:B4) = { Mon, Tue, Wed }	Mon	Tue	30
2	=UNIQUE(B1:B4, FALSE) = { Mon, Tue, Wed }	Tue	Tue	20
3	=UNIQUE(B1:B4, FALSE, FALSE) = { Mon, Tue, Wed }	Tue		10
4	=UNIQUE(B1:B4, FALSE, TRUE) = { Mon, Wed }	Wed	Mon	20
5	=UNIQUE(B1:B4, TRUE) = { Mon ; Tue ; Tue ; Wed }
6	=UNIQUE(B1:B4, TRUE, TRUE) = { Mon ; Tue ; Tue ; Wed }		Mon
7	=UNIQUE(B1:E1, TRUE) = { Mon ; Tue }
8	=UNIQUE(D1:D4, FALSE) = { 30 ; 20 ; 10 }
9	=UNIQUE(D1:D4, TRUE) = { 30 ; 20 ; 10 ; 20 }
10	=UNIQUE(FILTER(C1:C6, C1:C6<>"")) = { Tue ; Mon }
11	=SORT(UNIQUE(FILTER(C1:C6, C1:C6<>""))) = { Mon ; Tue }
12	=UNIQUE(B1:B4) = #SPILL!
13	=UNIQUE(C1:C6, , TRUE) = #CALC!

1 - What are the unique values in the vertical range "B1:B4".
2 - What are the unique values in the vertical range "B1:B4".
3 - What are the unique values in the vertical range "B1:B4".
4 - What are the unique values in the vertical range "B1:B4" when the "occurs_once" argument is True.
5 - What are the unique values in the vertical range "B1:B4" when the "by_col" argument is True.
6 - What are the unique values in the vertical range "B1:B4" when the "by_col" argument is True and the "occurs_once" argument is True.
7 - What are the unique values in the horizontal range "B1:E1".
8 - What are the unique values in the vertical range "G1:G4" comparing all the rows.
9 - What are the unique values in the vertical range "G1:G4", comparing all the columns.
10 - What are the unique values in the vertical range D1:D6, removing all the blank cells.
11 - What are the unique values in the vertical range "D1:D6", removing all the blank cells and sorting by the first column.
12 - What are the unique values in the vertical range "B1:B4". There are not enough blank cells to display the full array.
13 - What are the unique values in the vertical range "D1:D4" when the "occurs_once" argument is True. There are no items occurring only once.