(upbeat music)
- [Curt] One very useful way
to analyze your data in Excel is
to identify unique values within a data set.
For example, you might look at your orders
and see how many different states are represented.
In this movie, I will show you how to use VBA
to create a unique values filter,
and also how to use the new unique worksheet function.
I'll start with the VBA code
that we'll use to create a unique values filter,
and that filter will appear
in the same position as the original list.
The data will work with
run from cell A2
to A9,
and the code will refer to that range.
I'll press ALT F11 to move to the visual basic editor.
And here at the top,
I have a code module
which will use the current region
and create a filter.
The code to create this filter
is actually fairly straightforward.
We have the range, which is the string address,
which we get from the current region
based on the active cell.
So one limitation of this code, as it's written,
is that you need to make sure that
the active cell is within the data range
that you want to filter.
So I'll press ALT F11 to move back
and A2 is selected. That's good.
Go back. Next, we are using the advanced filter method.
That's good.
And the action parameter has Excel filter in place.
So instead of placing the results of the filter
elsewhere in the workbook,
we are going to just filter it in the same location.
And then after a comma,
we have the unique parameter set to true,
and that is the keyword that we need to remember
for this particular filter.
So we're filtering the values in place
and unique is set to true.
I'll click within the body of the sub-routine
and press F5. It's run.
Press ALT F11,
and you can see that we have all the unique values.
In this case, it was rows two, three, four and five,
which were the first occurrences.
And we go from five to 10.
So we know that rows six through nine are filtered.
I will go up to the editing group
on the home tab
and I'll click sort and filter and I'll click clear.
And that clears the filter from the active region.
If you have a recently updated version of Excel
for Office 365,
then you probably have the unique function available to you
and unique, as you might imagine,
finds unique values within a data range.
So I'll click cell D2, type an equal sign,
and then the function name is unique,
and the array we'll work with is
cells A2 through A9 as before,
and type a right parentheses and enter.
And you see that we get multiple values.
If this is the first time that you have used unique
or a similar formula,
you might've gotten an indication or message box
explaining what happened
that the value spilled from cell D2
down to other cells in the worksheet.
So don't worry about that. That's normal behavior.
It's brand new and it's still being worked on,
but as you can see, it's extremely useful.
That's how the unique function works in a worksheet.
Now let me go to the visual basic editor
and I will show you what it looks like
if you record what I just did using VBA
or in this case, the macro recorder.
So ALT F11,
and I will
move down.
And that's what the code looks like.
I don't know how to interpret that.
If you run the code, it will replicate what just happened.
However, I don't find it to be extremely useful.
So instead, what I usually do is
use the unique items sub-routine that I have at the start.
You might wonder, if you're familiar with Excel VBA,
If the unique function is available
as part of the application objects,
worksheet functions collection.
It is, and I have the code
for what that looks like here.
However, there is an issue with it
that you'll see in a second.
So if I click inside of this last sub-routine here,
which I called unique worksheet,
this code will activate cell F2,
and then it will find the unique values
from the range in A2 to A9
that we've been working with before.
So I'll press F5 to run it.
ALT F11 to move back to the worksheet
and you can see we only got one value.
So it appears that spilling isn't fully implemented
when you run the unique function
using VBAs worksheet functions collection.
So again, if you need to use unique,
do it within a worksheet as a formula.
Before I end this movie,
I would like to demonstrate a nuance or a limitation,
or perhaps a characteristic of Excel filter in place.
So if I go back to cell B1,
and let's say that I add another column of values
and I will call that status.
So for Washington, I'll put one, Oregon two,
California three, Arizona four.
So those are all different. They're all different rows.
However, if I type one again for Washington,
and then five, six, and seven,
what I have are seven unique rows.
So I have
A2
and B2, which are one row.
And because I'm looking at the active region,
sells A2 and B2 are considered a single item.
So it's a single row.
So that means that A2 and B2 is the same as A6 and B6.
However,
A5 and B5, which is Arizona four,
is of course different than Arizona seven.
To demonstrate what happens, I'll press ALT F11
and go back up to unique items at the top,
press F5 to run. ALT F11 again,
and you see that we only have one duplicate row missing.
And that is the second instance of Washington one.
I'll clear the filter and show you how to fix
this occurrence in this case.
So I'll click clear and then I'll ALT F11 again.
Now, instead of looking at the string address,
in other words, the current region,
I'll just look at cells in column A.
So I'll type
in double quotes, A colon A.
So this is a reference that just refers to column A.
I'll press F5 to run, ALT F11 again,
and you can see that we have
the four unique states
because Excel only looked at the values in column A.
(upbeat music)