0:00 So I thought this would be very interesting to you because it was interesting to me. For years, I've been using filter, the filter function, and not knowing necessarily how to combine them into one column.
0:12 So what we're doing here is we're taking our data over here on the right from the D column and G to G column.
0:17 We have different months, different regions. Different sales for different salesmen. And we just want to see a little summary in one column of who got what sales in which month.
0:29 And if I just change B2 to March, the sales automatically update. How did I do this? Well, we're combining two filters.
0:36 If you notice, we can, if you notice, we know the filter already. We can do something like this, filter, and we can take a whole range or maybe we just want, like, these two ranges, D and E, and we can filter it by some condition.
0:55 We're going to say, actually, the, uhm, like, the G column. M is equal to March. We can do D colon E here and get the same, uh, we just need to get the seam row sizes.
1:17 So we see here, this is the seam. Same thing we have over here on the left, but it is in two columns.
1:23 We're getting filter for the D column and filter for the E column. So how can we combine these into one cell, or one column, actually?
1:35 What we're doing is we're actually going to take each of the filters. We're going to create two different filters. There's one for the, one for D column, one for the E column, and then combine them with array formula.
1:46 We also have to use a little bit of a trick here, which is the ampersand, which is a kind of concatenate without using concatenate formula or join formula.
1:56 The thing with join formula is that it does not work with array formula. So we have to use some other way to concatenate or combine or join these different cells together.
2:07 And so, so let's look over here and see what we're doing and try to build this from the ground up.
2:20 First we're going to filter just the rep. And we're going to filter it by G column is equal to March.
2:33 But we don't have to use the text March. We can use a cell reference here which is going to be B2.
2:40 And we have just the names. But we can also change this D to E and we get the sales. We can change it to F and we'll get the region.
2:56 But for our case, we just need D colon D first. And then we're going to use array formula around it.
3:05 We're going to wrap this filter with array formula. But we're going to add an ampersand and another filter, which is going to be the E column.
3:16 And let's look at that. We have a name, I think we used, we misspelled. Array formula, there we go. Okay, so now we have the parts, but they're sort of next to each other, a little too close.
3:29 So we need to separate them so we can read this easier. So instead of just ampersand, we're going to do ampersand, going to do right in between here.
3:39 So let's put that up here. Another ampersand, and in between that, we'll put something in quotes, which is going to be a space, pipe, space.
3:49 There we have Andy, Dwight, Jim, we have each of their sales, and we want to add at the end another ampersand and the word space sales.
3:58 And so we're now we get a nice little summary in column B of the filters that we want. And we can say, we can even add more to this.
4:12 We can do ampersand, filter, let's say F. By the same, G colon G equals B2, let's put a space here, and after that we have resales in the, put another ampersand, space.
4:40 region. So now we have a nice little report that's very easy to see, very easy to read. Instead of all of our data, we can put it all here.
4:51 10 sales in north region, 12 sales in south region. As we change this to, let's say, January, it'll change the sales.
5:00 Very nice. So we're using a filter, the ampersand, which is sort of a join, and array formula to do this, to combine filters into one column.