Abstract
Transcript
In this video, we’ll introduce theUNIQUE 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,arrayis the source data you are working with.
The second argument,by_colmeans 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.
UNIQUE has just one required argument, calledarray.
I’ll undo that change.
UNIQUE has another argument calledexactly_oncethat allows you to retrieve unique values that appear just once in the source data.
By default, this argument is FALSE.
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.
I need to set theby_columnargument 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 attempt to setexactly_onceto 1 and leaveby_columnblank, it doesn’t work.
