# 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.