In this article, we will explore how to get unique values in Excel using the UNIQUE function and dynamic arrays. We will cover various scenarios, including finding unique values in a column or row, in multiple columns, based on conditions, and more. We will also discuss how the introduction of the UNIQUE function in Excel 365 has simplified the process of extracting unique values.
Introduction to the UNIQUE Function in Excel
The UNIQUE function in Excel is a powerful tool that allows you to extract a list of unique values from a range or array. It works with any data type, including text, numbers, dates, and times. The result of the UNIQUE function is a dynamic array that automatically spills into neighboring cells vertically or horizontally.
The syntax of the UNIQUE function is as follows:
UNIQUE(array, [by_col], [exactly_once])array(required): The range or array from which to return unique values.by_col(optional): A logical value indicating how to compare data. If set to TRUE, it compares data across columns. If set to FALSE or omitted, it compares data across rows.exactly_once(optional): A logical value that defines what values are considered unique. If set to TRUE, it returns values that occur only once. If set to FALSE or omitted, it returns all distinct values in the range or array.
Please note that the UNIQUE function is currently only available in Excel for Microsoft 365 and Excel 2021. It is not supported in Excel 2019, 2016, and earlier versions.
Basic UNIQUE Formula in Excel
Let's start with a basic example to understand how the UNIQUE function works. Suppose we have a range of names in column B, and we want to extract a list of unique names. We can use the following formula:
=UNIQUE(B2:B10)In this formula, we specify the range B2:B10 as the array argument. Since we want to compare the names in the range against each other, we omit the by_col argument. The result of this formula will be a spill range containing all the unique names from the range B2:B10.
Finding Unique Values in Multiple Columns
The UNIQUE function can also be used to find unique values in multiple columns. To do this, we include all the target columns in the array argument. For example, if we want to extract unique values from columns A and B, we can use the following formula:
=UNIQUE(A2:B10)This formula will return a spill range containing the unique combinations of values from columns A and B. Each row in the spill range represents a unique row from the original data.
Sorting Unique Values Alphabetically
To sort the unique values in alphabetical order, we can use the SORT function in combination with the UNIQUE function. The SORT function allows us to sort the results of the UNIQUE function based on a specified column or in a specific order.
For example, if we want to extract unique values from columns A through C and arrange the results in alphabetical order based on the values in column A, we can use the following formula:
=SORT(UNIQUE(A2:C10))This formula will return a spill range containing the unique values from columns A through C, sorted alphabetically based on the values in column A.
Concatenating Unique Values from Multiple Columns
In some cases, we may want to concatenate the unique values from multiple columns into a single cell. To achieve this, we can use the ampersand (&) operator to concatenate the columns and specify the desired delimiter.
For example, if we want to concatenate the first names in column A and the last names in column B, separating the values with a space character, we can use the following formula:
=UNIQUE(A2:A10&" "&B2:B10)This formula will return a spill range containing the concatenated full names in a single column.
Filtering Unique Values Based on Criteria
The UNIQUE function can also be used to filter unique values based on specific criteria. To do this, we can combine the UNIQUE function with the FILTER function.
For example, if we want to extract a list of winners in a specific sport, we can input the sport of interest in a cell (let's say F1) and use the following formula:
=UNIQUE(FILTER(A2:B10, C2:C10=F1))In this formula, we specify the range A2:B10 as the array argument and the range C2:C10 as the criteria_range. The criteria is set to the value in cell F1. The result of this formula will be a spill range containing the unique names of winners in the specified sport.
Conclusion
In this article, we have explored how to use the UNIQUE function in Excel to extract unique values from a range or array. We have covered various scenarios, including finding unique values in a column or row, in multiple columns, based on conditions, and more. The UNIQUE function, along with dynamic arrays, has made the process of extracting unique values in Excel much simpler and more efficient.
Remember, the UNIQUE function is only available in Excel for Microsoft 365 and Excel 2021. If you are using an earlier version of Excel, you may need to use alternative methods to find unique values.
That concludes our discussion on how to get unique values in Excel using the UNIQUE function and dynamic arrays. We hope you found this article helpful and informative. If you have any further questions, feel free to ask.