UNIQUE |
UNIQUE(array [,by_col] [,occurs_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 = 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 |
|
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