Introduction to FILTER

Quick reference

• 00:04 A really useful function when working with dynamic arrays is Filter.
• 00:09 What this does, it allows you to restrict the returns of your dynamic array based on
• 00:13 specific criteria.
• 00:15 We're going to look at that in this video, but before we do,
• 00:17 I just want to call out a couple of important things around the structure
• 00:20 of the spreadsheet that I have set up here.
• 00:22 The first one is all of my data in here is already sorted in
• 00:26 alphabetical order by category, and then by years.
• 00:30 I didn't need to do this.
• 00:32 I only did it, because I don't want to have to include a sort function in
• 00:37 every formula that I write.
• 00:39 In addition, you'll notice that we do have a couple of dynamic arrays over here.
• 00:42 We've got our unique sales group.
• 00:44 And we have our unique years which are actually using
• 00:47 a sort function to sort in descending order.
• 00:50 These are important, because I also have some data validation
• 00:54 list set up that are pulling from those dynamic arrays.
• 00:57 And you can see that if we go into data validation, the source of this particular
• 01:01 one is F13, but notice that hash mark at the end there.
• 01:05 That means it is pulling from the dynamic array, and
• 01:07 will expand and collapse as we end up with new unique values in that list.
• 01:11 The same is true of our years,
• 01:13 we have those set up to be pulling from the additional area here.
• 01:17 Finally, I have a nice little counting formula down here that checks to see
• 01:21 whether or not this particular cell is blank.
• 01:23 If it is, it'll return 0, and if it's not,
• 01:26 it provides a count a of the dynamic array that I'm expecting to write in this area.
• 01:31 Why do I have the f test on there?
• 01:34 Well, the reality is the count a will return a count of one,
• 01:38 even if the array is blank.
• 01:40 And now that you know how all of this is set up,
• 01:42 let's get into what we're really here for.
• 01:45 I want to extract a list of the unique categories where the group is alcohol.
• 01:52 Or food if I decide to change to that.
• 01:55 So what I'm going to do is I'm going to start by writing a filter command
• 01:59 to actually filter down into just those records.
• 02:02 And we're going to start here with equals filter.
• 02:05 And it says, all right, what's the array that you want to filter?
• 02:07 And I'm going to go and grab the Categories list.
• 02:10 And then it says, all right, no problem.
• 02:12 What records would you like to include?
• 02:15 So what I'm going to do is I'm going to choose where the group equals, and we'll
• 02:20 pick up our cell K9, we're going to close the parenthesis on this and hit Enter.
• 02:25 And what you'll see is that we now get a list that shows all of the different
• 02:28 categories that are related to alcohol.
• 02:31 The thing is, is that as the dynamic array works down here, it finds bottle beer,
• 02:35 it says alcohol, so will include that.
• 02:36 Finds bottle beer, it's alcohol, will include that and so forth.
• 02:39 And this is why we end up with a bunch of duplicate values.
• 02:43 Not a big deal though, we know we can take care of those.
• 02:46 All we need to do is wrap the entire function in unique.
• 02:51 And when we hit Enter, we're good.
• 02:52 We now have just the unique alcohol categories.
• 02:55 And if I go and change my drop down,
• 02:57 you'll notice that we can flip very quickly to food.
• 03:01 So this is pretty cool.
• 03:03 Let's go and take a look at how we would do this with year.
• 03:06 Now, again, I'm going to start with a filter and I'm actually doing this,
• 03:09 I'm not starting with unique.
• 03:11 Because I don't want to be trying to actually apply a filter after
• 03:14 the unique is done.
• 03:15 Because then my records won't line up.
• 03:17 So here we go, we're going to go with our filter.
• 03:20 I'm going to pick up Category again.
• 03:22 The records that I'm going to include are where years equal 2012.
• 03:29 I'll close the parenthesis on this and hit Enter and there you go.
• 03:33 We've got all of our individual records in this particular case.
• 03:37 It looks okay here, although, I would probably still wrap it in unique.
• 03:41 The reason being is there's actually only one record in this table for every year.
• 03:46 So that's why we don't actually have to have it in this case.
• 03:49 So if I were releasing this for consumption inside the organization,
• 03:53 I would probably wrap this in unique as well.
• 03:55 But as I say, because there's only unique values in every single year,
• 03:59 it doesn't matter.
• 04:01 All right, so that is our 2012 year, so everything is equal to 2012 in this case.
• 04:06 What if we wanted to do a filter where we're only pulling back categories that
• 04:10 are less than 2012?
• 04:11 Well, let's go and take a look at that.
• 04:14 So we'll go again and we'll say equals filter.
• 04:17 The array we're going to pick up is Category.
• 04:21 The inclusion is going to be where
• 04:26 years are less than 2012.
• 04:31 Close the parenthesis and hit Enter.
• 04:32 And again, everything looks pretty good here.
• 04:35 But let me show you where the problem comes in and
• 04:37 why unique might be necessary.
• 04:39 Again, it's only one record per year here, but
• 04:42 what happens if we change this up to 2013?
• 04:45 Well, now suddenly,
• 04:46 we've got duplicates, because we're picking up the years 2011 and 2012.
• 04:50 So in this case, it makes a lot of sense to come back and say,
• 04:55 let's go and wrap this in unique.
• 04:57 And what we can see now, if we move back to 2012, it's still going to work and
• 05:02 we can see that there's one category last year.
• 05:05 Because in 2012, we had liquor modifiers, but in 2011, we did not.
• 05:13 The last one I want to show you is honestly pretty similar.
• 05:16 What we're going to do here is we're going to work with excluding
• 05:20 a specific year.
• 05:21 So once again, we're going to come back,
• 05:23 we're going to start this time right away with our unique.
• 05:25 Because we know we're going to need it here as this will be returning
• 05:29 multiple years.
• 05:30 We're going to do a filter again and grab our Category.
• 05:35 And once again, we're going to set our year, but
• 05:38 this time what we're going to do is we're going to go with less than, greater than.
• 05:42 And this is the symbol that we're going to use to say not equal to.
• 05:46 So basically, I want records that do not have anything to do with the 2012 year.
• 05:52 I'm going to close the parenthesis first for my filter, and then for
• 05:55 my unique function.
• 05:56 And now, when I hit Enter,
• 05:58 you'll see that I get a list that is actually quite similar to the 2012 year.
• 06:02 But if I now go back and change this, you'll see we get a different set of
• 06:05 records when I say, don't give me the stuff that's equal to 2011.
• 06:09 I now have 16 categories and they do not equal 2011.
• 06:13 I have 14 categories in less than 2012.
• 06:16 And I've got 15 in 2012 itself.
• 06:19 So we've got different results here based on the way that we filtered our data and
• 06:23 it's all completely dynamic, which is pretty awesome.

Lesson notes are only available for subscribers.