Instructor: All right, time to talk about another one
of my favorite dynamic array functions, UNIQUE.
And what UNIQUE does is it removes duplicates
from an array and returns only the unique records.
So syntax wise, pretty straightforward.
Just one required argument, which is the array
of cells that you wanna remove duplicates from.
And then you have two optional arguments here.
The first is the by col or by column argument.
You'd specify a true or a 1 here
if you want excel to look column by column
to remove duplicates.
Otherwise, you can leave it blank
or specify false or 0 to go row by row,
which in many cases is what you want.
And then finally, you have this last optional argument.
Exactly once.
If you specify a true or a 1
you're only gonna extract values that appear once
and exactly once in your array.
Otherwise it's gonna extract all uniques
even if they appear multiple times,
which is the default behavior.
So a quick example here, again, looking at product
and category data.
We could use a UNIQUE function and reference cells
B2 through B10 to extract a unique list of category values.
In this case, electronics, sports, clothing,
music, and games.
Even though some of those categories
appear in multiple rows.
And if for whatever reason we wanted
only the categories that appear one time
in one single record or row within our array
we could use that optional third parameter here.
So we're specifying false for the by column argument
and true for the exactly once argument.
And that's gonna return only two category names,
music and games.
Because music only appears in row 5
and games only appears in row 6.
And a little pro tip here, in this example
we're only referencing a single column
in the array, the category column.
But if you include multiple columns in your array reference
what Excel is gonna do is return each unique combination
of values across those columns.
And I'll show you an example of this in just a minute.
So with that, that's the UNIQUE function.
Let's jump into Excel and practice applying this
to our project data.
Alright, so if you're following with me,
open up your section 9 workbook, head to the UNIQUE tab.
And here we've got our familiar Glassdoor data.
We've got industries, job titles, salary and growth
here in columns A through D.
And we're gonna use the UNIQUE function
to do a couple of things.
We're gonna return a unique list of industries
and then we're gonna check if there are any industries
that only appear once.
And because our data is at the job title level
that's the granularity of our data,
that could translate to any industries
that only have one single job title represented.
So let's start here in cell F3.
I'm gonna use a unique function to just grab
the unique list of industries in column A.
So A3 through a 86, close it off, press enter.
And there you have it.
We've got our unique list of 17 industries.
Now I mentioned with that pro tip
you don't have to reference a single column array here.
We could reference columns A and B.
And what that's gonna do is look for any unique combinations
of those two fields.
So in this case, if there were the same job title
within the same industry, those duplicates would be removed.
In this case, we don't actually have any duplicates
at that level, so I'm gonna just undo that change
and go back to our column A reference.
And then last but not least
if we wanted those one job industries,
the industries that only appear once
we can start the exact same way,
UNIQUE A3 through A86.
I can comma to the next argument
which is the by column argument.
And I don't want to change anything here.
I can either type in a false or a 0
which is kind of the default
or it could just press another comma to bypass
that optional argument entirely and specify a true
or a 1 here to indicate that I only want values
that appear exactly once.
So close it off.
Enter.
There we go.
I get two industry names returned,
real estate and design.
And just with a quick scroll through
I see real estate does in fact only appear once.
There's one job title associated
with a real estate industry, and that's property manager.
And similar story with design here at the bottom.
Just one job shown in our dataset,
which is graphic designer.
So there you have it.
Quick primer on how to use
the UNIQUE dynamic array function.