Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:05,010 --> 00:00:12,450
In the last lesson, I showed you how you can use the advanced filter to extract a unique list of values,
2
00:00:13,200 --> 00:00:21,450
and I mentioned in that lesson that now in Excel, we have a brand new four Excel 2021 function that
3
00:00:21,450 --> 00:00:23,190
will do exactly the same thing.
4
00:00:23,850 --> 00:00:27,600
And not surprisingly, it's called the unique function.
5
00:00:28,290 --> 00:00:35,100
And I have to say that if I need to extract a list of unique values, this is my go to method.
6
00:00:35,580 --> 00:00:41,760
I don't tend to use the advanced filter as much as I do unique, and I'm sure this is one of those functions
7
00:00:41,760 --> 00:00:44,190
that you're going to find yourself using all the time.
8
00:00:44,400 --> 00:00:47,720
Now, the unique function does exactly the same as the advanced filter.
9
00:00:47,730 --> 00:00:51,030
It lets us extract a unique list of values.
10
00:00:51,750 --> 00:00:59,430
So if I want to use the unique function to extract a unique list of all of the sales reps, all I need
11
00:00:59,430 --> 00:01:05,010
to do is click in a cell and type equals unique.
12
00:01:05,940 --> 00:01:06,960
Press the tab key.
13
00:01:07,320 --> 00:01:13,650
Notice I have three arguments now, the last two arguments are optional arguments they're showing in
14
00:01:13,650 --> 00:01:14,640
square brackets.
15
00:01:14,940 --> 00:01:22,330
So if you simply just want to extract a unique list, you only need to specify the array argument so
16
00:01:22,330 --> 00:01:30,960
that the array argument is basically the column that contains the unique values be five to be 28.
17
00:01:31,740 --> 00:01:38,040
So all I need to do is select it, close the bracket, hit enter, and I get my unique list of values.
18
00:01:38,220 --> 00:01:44,610
And because this is a dynamic array, if any of these were to change, this list is going to update
19
00:01:44,610 --> 00:01:45,150
as well.
20
00:01:45,690 --> 00:01:52,410
So if I was to change one of these to mark, you can see it now, adds Mark onto the end of the unique
21
00:01:52,410 --> 00:01:53,790
list of sales reps.
22
00:01:54,150 --> 00:01:59,250
Now, once again, if I was to add something to the bottom of this list, so if I just drag this down
23
00:01:59,520 --> 00:02:07,200
and let's say, Matthew, it's not going to update that unique list because we've chosen to extract
24
00:02:07,200 --> 00:02:14,700
this unique list from this range only it stops short of any new items added on to the end.
25
00:02:15,360 --> 00:02:21,960
So of course, we could combine this with a table which will auto expand and add dynamic array will
26
00:02:21,960 --> 00:02:23,280
update automatically.
27
00:02:23,820 --> 00:02:25,140
So let's just take a look at that.
28
00:02:25,140 --> 00:02:29,910
Let's click in our data control t yes, my table has headers.
29
00:02:29,910 --> 00:02:30,710
Let's click on.
30
00:02:30,720 --> 00:02:37,500
OK, now I'm going to give my table a name, so let's click on table design and in the table name group.
31
00:02:37,590 --> 00:02:41,640
Let's call this sales reps and hit enter.
32
00:02:42,570 --> 00:02:46,950
So now I can use this table and this will be completely dynamic.
33
00:02:47,640 --> 00:02:52,950
So I want to extract my unique list from the sales rep column and notice.
34
00:02:52,950 --> 00:02:57,450
Because I've put this data into a table, the annotation is different.
35
00:02:58,020 --> 00:03:03,060
It's showing me the table name, which is sales reps, and then I have the column that I've selected
36
00:03:03,060 --> 00:03:04,350
in square brackets.
37
00:03:05,160 --> 00:03:09,630
So if I close this and hit enter, I get that same unique list.
38
00:03:09,900 --> 00:03:16,320
But if I was to add another entry on the bottom, that's just drag this down and add in my view when
39
00:03:16,320 --> 00:03:17,370
I press the tab key.
40
00:03:17,580 --> 00:03:22,080
You can see that that unique list now dynamically updates.
41
00:03:23,910 --> 00:03:27,570
So just bear that in mind when you're working with this unique function.
42
00:03:27,840 --> 00:03:32,550
Now, as I mentioned, when you're using unique, there are some other arguments, some optional arguments
43
00:03:32,550 --> 00:03:33,150
that we have.
44
00:03:33,150 --> 00:03:35,670
So let's just take a look at how those work.
45
00:03:35,760 --> 00:03:40,520
So let's type equals unique again, the array.
46
00:03:40,530 --> 00:03:42,690
Well, let's stick with the sales rep column.
47
00:03:45,030 --> 00:03:45,590
Comma.
48
00:03:46,500 --> 00:03:53,640
Now I can specify if I want to return unique columns or unique rows, so if I was to choose the true
49
00:03:53,640 --> 00:03:59,280
argument here to return the unique columns, if I had column headings that were repeated running across
50
00:03:59,280 --> 00:04:03,630
the top, I could use the true argument just to extract the unique columns.
51
00:04:03,870 --> 00:04:09,930
I will say that most of the time you're going to use this to return unique rows, and that is in fact
52
00:04:09,930 --> 00:04:12,420
the default if you don't specify this option.
53
00:04:12,960 --> 00:04:14,520
So let's just type in false.
54
00:04:15,210 --> 00:04:21,660
And the last argument we have here allows us to either return items that appear exactly once in our
55
00:04:21,660 --> 00:04:25,140
range or return every distinct item.
56
00:04:25,890 --> 00:04:32,190
So if I want to return the six individual sales reps, I would use the false argument.
57
00:04:32,550 --> 00:04:36,330
And again, that one is the default if you don't specify anything.
58
00:04:36,960 --> 00:04:43,200
But what about if I just wanted to see sales reps that appeared exactly once in my selected range?
59
00:04:43,860 --> 00:04:49,270
And because I've just added Matthew on the bottom here, he only occurs once in this list.
60
00:04:49,830 --> 00:04:56,700
So if I just wanted to see Matthew, I could use true on the NTA because it's going to pull back values
61
00:04:56,700 --> 00:04:59,700
that occur exactly once you enter.
62
00:05:00,030 --> 00:05:01,260
Oh, I actually have to.
63
00:05:01,260 --> 00:05:02,000
Yes, I do.
64
00:05:02,010 --> 00:05:03,190
I've got Mark in here.
65
00:05:03,210 --> 00:05:06,390
He only occurs once as well and also Matthew.
66
00:05:07,260 --> 00:05:11,000
So those are the additional arguments that you have when using.
6782
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.