00:00 Hey, so welcome to this video about filter spoiler alert, we're going to talk about the filter function to save your life.
00:08 If you have a task list or you have some kind of a list of things where you have different statuses, this could be sales, this could be anything you have in the- individual little tasks that have individual different statuses.
00:24 In this case, I have a friend who has this particularly interesting way to categorize their work and they have one particular thing that I will save for the later in this video that really hard to overcome.
00:39 Basically they've- created a big job, little jobs, and tasks. And each of these tasks have a different status. They have maybe that's in progress, maybe they switch something to like, oh, I'll never be able to do that.
00:52 Or they'll say, hey, I'm going to do this next week or I'm going to get this started now, but I want to know like what is started and what's not.
01:00 And by- doing this, you might have a different setup than this on the left, but you might have something like C and D column here, these tasks and these statuses.
01:09 And so that's going to be the filter function that we're going to go into, why the filter function is going to save you from overload here.
01:15 But there's one extra thing that I think is really cool that makes, ends up making the way that my friend.
01:21 And does this actually much better. So he does not merge these cells. So he has something like actually this. And what I noticed is that there's sort of this big job, a little job, and then bunch of tasks.
01:35 And these are very hard to filter. And I'm like, I love filter. I think filters. The coolest function be after if.
01:44 Okay. In my top five of functions, I think the if function is definitely the best one. I think it's just magical.
01:51 But the filter function ends up being sweet number two. And so what I did here is on this tab I called next week.
01:59 And I'm like, I really just want to know all of these tasks. What am I gonna what's on my plate for next week?
02:05 Maybe I am putting this into my calendar, or maybe I'm like letting someone else into my system. Maybe this is for VA's and you're like, you know, you've assigned different VA's different tasks.
02:16 And hey, I want to make sure I have the right amount of right amount of work for this. One person so maybe instead of statuses, these are like who it's assigned to.
02:26 This will all work. So we're gonna just select some random stuff here. Just to give it some extra stuff here.
02:35 But on this next week tab, what I did is I added one more drop down and I set this drop down to be.
02:42 You can set it as the exact same drop down as your status list. Like literally just copy paste it. But what I also, what I did just in case you don't have like statuses in a drop down.
02:53 I did drop down from a range and I set the range to be the D column. This entire status column.
03:00 That's what I did. That's. Yeah. One way you can do it if maybe you don't have these statuses already in a drop down.
03:06 Okay. So this is what helps me get over overload is this filter function. And I'll set up another one. So this filter function, I can see that I have, you know, big job here, little job.
03:22 Task. And what I do is I filter the a column here and actually I just need, I'll just do the tasks.
03:32 Task. I just filter the c column for anything in the d column that equals this a one here. So I can set this to.
03:42 Next week. Now what's cool about this having a dash a dropdown and then this filter function is that I can duplicate this, call it started and then change this to started.
03:55 And now all my tasks are the ones that I've determined as started. That's pretty cool. That's pretty easy to then add.
04:02 Instead of these tabs, sort of all these tasks as different tabs are the, sorry, these statuses as different tabs. Now instead of having to do something like color these very differently and now I have to pick through all the colors and some people see colors in a different way and I just
04:22 do not. Prefer seeing colors at all. I like seeing actual like words or icons, something that's not colorful. And so now you can separate all of these tasks onto different tabs.
04:34 So instead of saying, what have I started and not finished yet? And then having to go through this and say, okay, here's the started here.
04:43 Started. Okay. There's these two tasks that I've started instead of doing that. I have them all in here, but my friend ends up having one weird problem is that he's broken up these tasks into little job, big job.
04:55 And he has these these really awful sort of, I say awful cause they're awful, but. They end up being really useful.
05:05 These tags, sort of a little job or a big job or some aspiration and then a big job and then breaks down the tasks that those jobs into different tasks.
05:16 If I were to style this differently, what I would do is I would definitely merge all of these big jobs.
05:23 This would help me visually and how I can actually color this a little bit. And here I would use a little bit of color to define sort of these different big jobs, but then also these little jobs, I would also merge and make sure that let's just put all of these as centered.
05:43 Vertically, so that when I do this merging, you see it does end up looking really nice. Sort of a bad data turns into a nice way to organize these different types of jobs.
06:00 And I'm going to center, center vertically and center horizontally. And now, how, having that space, I can see, oh, look at that.
06:08 Maybe I colored these also as yellow. And instead of this as yellow, maybe I have a whole different color range here.
06:16 Different color palette for it. Maybe I go down this color palette, like so. Something like this. Now, this is pretty cool.
06:28 Now, I can see, okay, there's a lot of more tasks to visit the grandparents than is the home renovation. Maybe that's a good thing.
06:36 Maybe that's a bad thing. It doesn't really matter in this case, right? It just allows us to see the difference.
06:42 Okay, this little job has two tasks. This little job has three tasks. Oh, and I'm never doing this one, so maybe I delete it.
06:49 You know, looking at this now, I can see different things because I see the size of these jobs, right? The size of these jobs and how many tasks are per each one of these.
07:02 But here's the thing. When I go over to my pay system, when I say, okay, my friend has this big job, little job, that's cool, right?
07:11 But it doesn't let me parse out these tasks by the status. And so this task is sort of orphaned, like create list of what?
07:19 I can't really tell on here. So what I need to do is I need to get in another column, because I want to sort of keep this way and this sort of cool looking way, but I also want to parse through these by status.
07:35 So what I want to do is I want to put in this column, big job. And if I just do A2 here, it's a merge cells of A2.
07:44 A2 to A6. Look, it doesn't copy over. So how do I copy it over? Well, I found this app script on a line sort of asking, how do you find the first in a merge column?
07:56 And so now and you're able to just go into the sheet as a better sheets. Remember, you can see this and grab it and grab this.
08:04 All right. Function, this custom function here. And what we do is we have it here first in merge column, and we just do column A2, row A2 to get the column in the row of the item that we want.
08:17 And then we just copy and paste it all the way down and it loads it up and it loads that big job.
08:22 So now. I have big job here. I'm going to delete this column here. It's going to reload every time I edit this page.
08:33 It's fine. It'll come back right away. So now I go to my next week and I go, okay, I want to know what big job this is part of.
08:41 So I'm going to go take this filter. And instead of the C column, I think it's E column. There we go.
08:50 There's the E column. So now I'm filtering the entire E column by whatever is in the D column for this next week.
08:58 Now I have the big job and the task. Isn't that cool? If you want the little job as well, we can add that.
09:05 Here, little job. I'm gonna take this first in merge column. I'm gonna change it to B2, and this one to B2 as well.
09:17 It should work out the same. I'm just gonna copy and paste it all the way down. We'll see. There we go.
09:23 There's the little job. So while I, Don't necessarily recommend having merged cells like this because the data is hard to navigate.
09:32 We have this sort of workaround with Apps Script, this first in merged column, which gets a column in the row and then finds that first in the merge column.
09:41 So we have a little workaround that we can create here. We have our ability to have everything. Now we have little job too.
09:52 And we can do filter. We're going to filter. It's on sheet one. The, what is it? The F column, I think.
10:03 F will fix this later based on the condition. That sheet one D colon D is equal to A1. There we go.
10:19 So now we have our big job, our We have our big job D colon D is equal to A1. So now we have our big job D colon Okay, we can duplicate this and we'll call it never.
10:31 And we'll see what have we set up to be never done. There we go. And now we can see all of our statuses in different places and really kills that overload really gets rid of that overload right now.
10:44 We can say, okay, here's what we've started. Here's what we have to do next week. Ah, that's great. Right? Fun.
10:50 Thought you'd like it. Bye.