How To Sort A Filter

Data validation Intermediate

About this Tutorial

This will allows you to analyze and organize your data.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div> Hello. Here is today's problem. So we have these filters, which are combining three sheets, Steve, Jerry, Susan, we have Susan, Steve, Jerry down here. And each of these have their own data . They might have some blank cells or some blank rows. And this filter takes care of getting rid of the blank cells or rose, and then combining it all into one. So we have our item name, our date, and your name. And what we want to do is we wanna sort one of these columns by something like we want to sort this date and if we typically go here, right? And sort A to Z . It is weird, right?

This isn't this sorting. We can sort like that and like it's just not sorting because as you can surmise, this is a filter and really that's the only data in here we can see if we delete anything, it's just filtering and we can't really sort this, but we can. But we need to do one more step. So let me redo this and I'll show you how. Okay. So we have each of these rows or rather columns, the all each of these columns has our own filter and we want to sort it. So what we really need to do is we could do here, is we can do sort filter. Or not even filter, sorry. Array formula.

And we can take A to C, it's a two to C, and then we can sort by column two in ascending or descending order and see you. Now we have, the dates are sorted, we can change the two to one. And we sort it. But how do we get, how do we get this? To be able to switch , right? To be able to change. Here's how here's the end result is I made a dropdown menu here and then added a switch in here. So this is a pretty unique , crazy little way to create a sort with data that you have from filters or an array of somewhere else. We're gonna add one more step.

We're gonna add this drop down, and then we're gonna add a switch and I'll show you step by step how I do. All right, so we have a filter . Let's create, let's duplicate this. And I'm gonna just delete it and actually I'm gonna delete this part and I'm gonna say, okay, we're gonna get, we're gonna do equals sort . And then in the sort , we're gonna take the range of filter . That's just the name of the sheet that here, A two to C. And now we do. Now which column do we wanna sort by the second one, which is the date? And we can say true. Let's say true here, right?

And now we just need to move this false. And you see the date sort . Okay? So now all we need to do is change this two. So what are we gonna change it to? Let's add up here. Data validation . Let's do list from items and just do date. Usually it's like Z to A to Z, and then comma date A to Z . I think that we'll do, let me double check and see what this sort is. So see the sort says sort sheet A to Z , Z to A, and that's your only options, right? So that's gonna be similar here. Let's call it Z to A, which is like the highest to lowest or lowest. Highest.

And you can name it anything you want here now for. We don't want to change the column, right? The two is the second column, but the false and true decide if it's high to low or low to high. So let's change this. Let's go switch and what are we gonna switch ? Gonna be one, which is where our data validation is, or dropdown menu. And in the case of date, we're gonna put our Values in quotation mark Z to A , we can do false if you don't remember which one is which. You can just put it in and then test it if it works out.

So I don't remember right now which one to which, so I'm just gonna put it in and we'll figure out afterwards if it works. Nothing changed. Nothing happened because I had this if already set. If I change it A to Z Oh, so now, Three, nine is the lowest date to the highest date, so A to Z and then Z to A is highest state to lowest state. So it's right. So false is high to low and true is low to high. And so this is on sorting on the second column. So we, what we're doing here is essentially saying, let's grab everything and just put it in a, in.

Tab here and then take this entire tab and filter it. Or sort it actually. So this should be called sort . And now we're here, we're sorting it. And you can say, Hey if you're, if you have to give this to someone else and be like, Hey, this is the only thing you should change on this tab. You can go and color it really bright blue. This is like my personal preference for anything that a user can change. Don't change anything else, just change this. And now we know that the only function in this entire sheet is over here in A two.

So if anything happens or you have to protect the sheet, you can protect this and let them edit this. Or just tell 'em, don't touch anything except the blue one here. And then you can also name this anything you want. You can say Date, high to low. Let's go here, let's do this. Let's go data validation . This is high to low and then low to high save. And all we have to do is come back to this formula or function here and change this switch . So we say this is ha low and this. Low to, oh, we have to get the low to high. High to low. And what's different here? Date? High to low.

Date high to low. Ah, so I had an extra space there. So now it's date high to low. And date low to high. And so you can name it anything you want. Really make it easy on people who are using this to know what are they sorting and where are they sorting. You can label it sort , label it, filter and people know, oh, this is the filter and this is the sort . You can also say, Hey, you want to get that same data ? Let's do one more. You want to sort the names, right? So instead of having this as the sort can move that over here. And we're gonna do item date. Let's do date.

And then these two are gonna be name data validation . Now we'll use name A to Z , name Z to A . We're gonna save. Now the only thing we have to change are the or we also have to change the column that it sorts. And then we have to change this, which goes name Z to. And this will be name A to Z and then we have to label it name A to Z . Ah, so here's what we did also wrong is the switch . We also have to fix where the expression is looking. So it's looking at f1. We have to make it G one, and that should. There. Oh, and then we have, we're filtering, when we moved this we moved the columns.

So we need to go back to A two to C. There we go. So now it's gonna name Z to A here and A to Z . So now we have these two ranges or these two tables that we can now sort by .

Ways you can say, you can even put these on different tabs and say, Hey, sort by date on this one, sort by name on this one, you can there is a little bit of a back and forth, like you can sort different things in different orders, but it's gonna be a whole nother order of magnitude of difficulty. But basically you can tell, you can sort it one way, then sort it again so that you have sort this, then this and this. And. Have groupings to a certain extent, but that's like the next step if you want to have a level of difficulty more.

But hopefully that helped you figure out how to sort a filter by.</div>