Transcript
In this video, we’ll introduce the UNIQUE function .
One of the new functions that comes with the dynamic array version of Excel is UNIQUE. The UNIQUE function lets you extract unique values in a variety of ways.
The UNIQUE function takes three arguments. The first argument, array is the source data you are working with. The second argument, by_col means by column. Use this when data should be compared across columns instead of rows. The last argument, exactly_once , tells UNIQUE to return values that only appear once in source data.
On this first worksheet, I’ve got a list of 16 fruits with some duplicates. Let’s use the UNIQUE function to extract a list of unique fruits.
To enter the function, I’ll type an = sign, then “uniq”, and then I’ll hit the tab key to confirm.
UNIQUE has just one required argument, called array . If you have a normal vertical list of values in rows, this is the only argument you need to provide.
When I give UNIQUE the range B5:B20, and hit enter, we get a list of unique fruits in a dynamic array .
If I overtype one of the values in the source data, you can see that the spill range immediately updates.
I’ll undo that change.
UNIQUE has another argument called exactly_once that allows you to retrieve unique values that appear just once in the source data.
By default, this argument is FALSE. But if I set the value to 1 or TRUE, you can see we get an even smaller list. These are the fruits that appear in the column B only once.
Since “apple” appears more than once, it is not included.
You’ll sometimes hear these values described as “distinct” as opposed to “unique”.
On the next sheet, I have the same data but in a horizontal format.
In C6, if I enter UNIQUE function without any arguments, you can see we don’t get unique values.
I need to set the by_column argument to 1 or TRUE to tell UNIQUE to compare values across columns.
Once I do that, we get the same list that we saw on the first worksheet.
The same is true if I want to extract fruits that appear exactly once in the source data.
If I try to set exactly_once to 1 and leave by_column blank, it doesn’t work. I need to set both arguments to 1 (or TRUE) to get a list of fruits that appear just once in the source data.
