Instructor: All right, so we talked about some
of the most powerful dynamic array functions, SORT,
SORTBY, FILTER and UNIQUE.
Now let's shift gears and talk about some ways
that we can combine or nest these functions together
to do some really powerful things.
And first and foremost, I wanna share a pro tip with you
that I use all the time, which is creating dropdown
or data validation lists using SORT and UNIQUE.
So for this demo,
let's jump straight into our Excel workbook.
You're gonna head
to the Combining SORT, FILTER & UNIQUE tab.
And our goal here is to come up with a unique list
of industries that will serve as a source range
for a dropdown cell or data validation link
which will live right here in J3.
So we're gonna start right in G3
to come up with our list of unique industries.
And as we already know,
the simplest way to do that is using a UNIQUE function.
And we're gonna point to our list of industries,
A3 through a A842.
Close off the parenthesis,
press Enter and there's our unique list.
The only catch is that by default, it's in the source order
which in this case is reverse alphabetical
which doesn't really make a whole lot of sense to the user.
I think if we're dealing with something
like a dropdown list or a selection list,
having it sorted A to Z
is probably the most logical approach.
And to do that, it's as simple
as just wrapping that UNIQUE function
that we just wrote in a sort.
And because Excel's default behavior is alphabetical A to Z
for text values, we can press Enter
and it just resorts it, design, education,
engineering, finance, all the way down
to technology right here in row 19.
So that looks good.
Very simple case of combining SORT and UNIQUE.
And now let's go ahead and tie that
to a data validation dropdown cell,
which will live here in J3.
So we can navigate to our Data tab, select Data Validation
and here we're gonna allow a list.
And the source of that list is that list
of unique industries that we just created.
So note that it populates a fixed reference here
from G3 through G19.
And for now, let's go ahead and close that out.
Press OK. So far so good.
We've got our list
of alphabetically sorted unique industries
from design through technology.
But here's the catch.
What if we added data or changed our industries
to create an additional unique industry
like data for instance?
Well now our dynamic array updates to incorporate
that new information, we've got data, design, education,
but check out our dropdown list.
Start at the top.
We've got data represented there, but we lost technology.
And remember the reason why is
because we created a fixed source reference
that only extended to row 19.
And now we have unique industries
going all the way down to row 20.
So to solve for that, we can use our handy pound
or hash sign to make sure
that this dropdown list grows as our dynamic array grows.
So let's go ahead, select J3,
head back to our data validation dialogue box.
And I'm gonna replace this source
that was automatically generated
and type it in, G3 pound.
Okay? And when I press OK, now watch what happens.
Data, design, education, all the way down to technology.
So it had grown to extend to row 20.
And we can test this again.
You know, databases, it's added
to our dynamic array and it's added to our dropdown list.
So now we have a perfectly flexible dropdown list
that can grow and shrink as our source data changes.
Let's go ahead and just undo that change.
I'm gonna replace those back to technology.
And there you have it, creating a sorted dropdown list
by combining SORT and UNIQUE.