UNIQUE

UNIQUE(array [,by_col] [,occurs_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 = Your data is arranged from top to bottom so you are comparing rows (default)
True or 1 = Your data is arranged from left to right so you are comparing columns
occurs_once(Optional) Whether to return values that occur only once:
False or 0 = Return all unique values (default)
True or 1 = Return only unique values that 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. Examples 4 & 9.
* If "occurs_once" is left blank, then False is used.
* If "occurs_once" = 0 or False, then all the unique values will be returned.
* If "occurs_once" = 1 or True, then only the unique values that occur once will be returned.
* If "occurs_once" = 1 or True and no values occur only once, 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 SORT function to return an array of data that has been sorted.
* You can use the DUPLICATEVALUES - User Defined Function function to return a list of items that appear more than once.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

 ABCDEFG
1=UNIQUE(B1:B4) = { Mon, Tue, Wed }MonTueTueWed 30
2=UNIQUE(B1:B4,FALSE) = { Mon, Tue, Wed }Tue Tue  20
3=UNIQUE(B1:B4,FALSE,FALSE) = { Mon, Tue, Wed }Tue Mon  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 }      
7=UNIQUE(B1:E1,TRUE) = { Mon, Tue }      
8=UNIQUE(G1:G4,FALSE) = { 30, 20, 10 }      
9=UNIQUE(G1:G4,TRUE) = { 30, 20, 10, 20 }      
10=UNIQUE(B1:B4) = #SPILL!      
11=UNIQUE(D1:D4,,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".
9 - What are the unique values in the vertical range "C1:C4".
10 - What are the unique values in the vertical range "B1:B4". There are not enough blank cells to display the full array.
11 - 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.

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