UNIQUE

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

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

arrayThe 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

 ABCD
1=UNIQUE(B1:B4) = { Mon, Tue, Wed }MonTue30
2=UNIQUE(B1:B4, FALSE) = { Mon, Tue, Wed }TueTue20
3=UNIQUE(B1:B4, FALSE, FALSE) = { Mon, Tue, Wed }Tue 10
4=UNIQUE(B1:B4, FALSE, TRUE) = { Mon, Wed }WedMon20
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.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top