FILTER-ing Dynamic Arrays with a Single Condition

Quick reference

• 00:04 What we're going to look at in this video is how to add single condition filters to
• 00:08 a dynamic arrays.
• 00:09 And that's where things start to get really, really powerful.
• 00:13 Now I've taken the liberty of setting up a couple of things in this
• 00:16 workbook that I want you to just recognize real quick.
• 00:18 The first one is we've got some grouping levels.
• 00:20 So I'm going to go and collapse this one on the left hand side first,
• 00:23 just to get rid of that header row.
• 00:25 So I have a little bit more room to work with, and
• 00:27 I'll play with the other ones a bit later.
• 00:29 I've also got four cells here that are light blue that have values in them.
• 00:32 And you'll notice each one of them has a drop down for a dynamic drop down list.
• 00:37 But that's the important part, they're dynamic, look, alcohol and food.
• 00:41 This one here is picking up our years,
• 00:43 which are coming from the dynamic array that's sitting in these cells.
• 00:48 How did I make that work?
• 00:50 Well, I went to set up my data validation rule through Data,
• 00:54 Data Validation, I set up a list.
• 00:56 But I couldn't refer to the unique formula that won't work.
• 01:01 So what I needed to do was actually land it using a helper column
• 01:04 in the grid first.
• 01:05 And then I can actually refer to it using the hash notation to pick up the dynamic
• 01:09 array spill range.
• 01:10 And this is unfortunate, you can't use a name range,
• 01:13 you can't use the direct formula here.
• 01:14 You have to lent the grid first and pull it in this way, but hey,
• 01:17 at least there's a way, that's the important thing.
• 01:19 We can always hide the column later.
• 01:22 So now we'll say, OK, and that's what I've done for these individual guys here.
• 01:26 The last thing I want to point out that I set up in advance was this nice little
• 01:30 category count column right here.
• 01:31 You'll notice the formula COUNTA, which is count all instances from I12 spill range.
• 01:37 So if I go and click on that, you can see that it's picking up from the cell.
• 01:40 Well, I'm going to write a formula here.
• 01:42 But right now, there's nothing there, so it says there's only one cell present.
• 01:46 So let's see what we can do.
• 01:47 Because I've got this really cool little list of categories over here.
• 01:51 But what I really want to do is I actually want to bring back the categories for
• 01:55 only alcohol items.
• 01:56 So how do I do that?
• 01:57 We use this cool little function called FILTER.
• 02:01 And what FILTER asks for,
• 02:03 it says what would you like me to actually return into your cells?
• 02:06 So I'm going to say well, I'd like to see the sales category, that's what I'm after.
• 02:11 And it says, okay, when I move to the next parameter says,
• 02:14 what would you like to include in this list?
• 02:16 Said, only like items to be included if the sales group equals alcohol.
• 02:24 And I'll just close the parentheses.
• 02:26 So what's going to happen is it's going to iterate through all of the categories,
• 02:30 it will check what the group is to see if it matches alcohol in cell I8.
• 02:34 And when I hit Enter, you'll notice that I now have 19 items are showing up.
• 02:39 The problem, Bottled Beer, Bottled Beer, Canned Beer, Canned Beer,
• 02:42 have come up twice.
• 02:44 So no big deal, let's go back and let's modify this.
• 02:48 UNIQUE, and we'll just close the parenthesis at the end.
• 02:52 And I'll be honest, this is the way that I build my arrays.
• 02:55 I will actually build the FILTER first and then I'll add the UNIQUE and
• 02:58 then I'll add the SORT then I'll add the TRANSPOSE or
• 03:00 whatever I need to do in order to make all this stuff work.
• 03:03 So layer them on gradually.
• 03:05 It's a really helpful thing because at least you can see what's happening.
• 03:07 So that's cool.
• 03:09 Let's go and see what we can do for the 2013 categories.
• 03:13 So we'll start again, we'll go FILTER, and
• 03:16 we'll choose to pick up the sales category.
• 03:20 And this time we'll say where
• 03:24 the sales years equals 2013.
• 03:29 And we'll close the parenthesis, and boom, we get a whole bunch of those.
• 03:33 We got 14 of them again, this seems like is a little bit too long.
• 03:36 So let's go back and again, wrap this in UNIQUE.
• 03:42 And now, when you hit Enter, well, we're still at 14.
• 03:46 Okay, no worries, maybe there wasn't any repeating items, at least now we know.
• 03:50 But here's the big thing that's important to recognize about this.
• 03:53 These are single column restrictions.
• 03:56 What that means, these are not 2013 alcohol categories,
• 03:59 these are 2013 categories, every single one of them.
• 04:03 That's why we got 14 of them.
• 04:04 Alcohol is restricted to seven across all years.
• 04:08 And this is actually something that's going to get kind of important to know.
• 04:11 Because if you go back and you pick up, say, I don't know 2012,
• 04:15 notice that now we have 15 categories.
• 04:17 There's something different in this one.
• 04:19 It's this one here, Liquor Modifiers.
• 04:21 If I go back to 2013 Liquor Modifiers disappears.
• 04:25 So that's kind of an important thing to be aware of.
• 04:27 And likewise, if I filter now from alcohol to food,
• 04:31 you'll see that those change nicely.
• 04:33 Now, that's the first examples that I wanted to show you.
• 04:36 What I'd like to now show you is how we can actually do something pretty amazing.
• 04:40 So I'm going to collapse this example and get it out of the way for a second.
• 04:43 And what I want to do is I want to go and
• 04:45 actually build some summary stats around this.
• 04:47 I want to know where the group is alcohol and where the year is less than or
• 04:51 equal to 2013.
• 04:52 So I'm actually working with two sort of conditions here in this case,
• 04:55 and this is a little bit tricky.
• 04:57 So what I'm going to do first is I'm going to come back and
• 05:00 I'm going to say I'd like to have my years across the top.
• 05:03 Now I am going to need a little bit of data out of what we actually have in this
• 05:07 particular area.
• 05:08 I want to refer to my years here to make this easy.
• 05:10 Let's just collapse the sales all the way.
• 05:12 So here's how it's going to go, equals TRANSPOSE, and
• 05:15 let's pick up the range here, and we get those items.
• 05:17 The problem is if I start toggling at this point,
• 05:23 less than or equal to and I hit 2012, nothing changes.
• 05:29 So I need to be a little bit smarter with this.
• 05:32 Now I'm going to do this a formula bar so we can actually read it.
• 05:34 Because it's not G12 that I actually want.
• 05:38 So let's show you how we work with ranges, FILTER,
• 05:42 we're going to filter the dynamic range.
• 05:45 Notice this the range reference.
• 05:46 And what we're going to do is we're going to say that we want to include
• 05:52 items where, the same dynamic reference, is less than or equal to 2012.
• 05:58 And close the parenthesis.
• 05:59 So, ideally this should go through and say, well 2013 is not less than or
• 06:03 equal to so forget it, don't include it.
• 06:05 And then of course,
• 06:07 we'll need to transpose it to make it go horizontally instead of vertically.
• 06:12 And at this point, we now have something that is
• 06:16 dynamically picking up our 2013 and 2012, nice.
• 06:21 For our sales, I now have two things that I want to pick up
• 06:24 because I obviously want alcohol sales for these years.
• 06:27 So, what I'm going to do at this point is I'm going to write a nice little formula
• 06:31 looks like this.
• 06:31 We're going to go some ifs because we have multiple conditions.
• 06:35 The SUM range comes first, that's going to be sales sales.
• 06:40 The criteria range is going to be our sales group,
• 06:47 which is going to be equal to alcohol.
• 06:51 And criteria range two is going to be sales years.
• 06:57 Now in this case it can be equal to again.
• 07:02 It's going to be equal to 2013.
• 07:05 Close the parenthesis.
• 07:07 I didn't spill, what's going on there?
• 07:10 You know what, why don't we try this again?
• 07:13 Why don't we add a nice little hash so it picks up the entire area, and
• 07:18 boom, just like that it works beautifully.
• 07:21 So what if I want to go even further?
• 07:22 What if I want my alcohol categories?
• 07:24 Well, we know at this point that we can do something like this.
• 07:28 Equals UNIQUE, FILTER, sales,
• 07:33 category, where the sales,
• 07:40 Group equals alcohol.
• 07:45 Close the parenthesis, close the parenthesis again.
• 07:49 And now we've got our categories down the left hand side.
• 07:52 What I'd really like to do now is populate the gult of this table,
• 07:56 which is going to be done with a sum if statement.
• 07:58 Equal SUMIFS Sales,
• 08:05 Sales again, our criteria at this point in time is going to be Sales,
• 08:11 Category, which equals Bottled beer.
• 08:14 Let's be smart the sign add the hash right off the bat, so we get the whole thing.
• 08:18 And where sales years is equal to 2013 hash,
• 08:26 close our parenthesis.
• 08:30 And just like that, we've basically built a pivot table.
• 08:35 But here's the really cool thing about this particular pivot table.
• 08:38 If we close this and open up our data range, a pivot table takes a refresh.
• 08:44 If I go 2014, and go wait a minute, something changed, 2011 change but
• 08:48 what else?
• 08:49 Well, guess what?
• 08:50 We don't have all of our data in here, 2014 boom.
• 08:55 This is what's really cool about dynamic arrays,
• 08:57 we can actually rebuild a live pivot table on the fly.
• 08:59 Some pretty neat stuff there.

Lesson notes are only available for subscribers.