- [Instructor] This new SORT function is pretty cool. Check this out, we've got names, regions, and hours. Now, let's do a straight out-of-the-box sort. In cell F4 we're going to type equals, sort, open parentheses. We want the array. And notice in the hint box, array is the only required component. Sort index, sort order, by column in those square brackets, that means those are optional. So, let's grab our array, it's this table. I'm going to move my cursor up over the headers. When I have that black arrow, I'm going to select that, slide over, that's my entire array, close parentheses, enter. All of the names are sorted in order. So the default was to sort by the first column, and sort ascending. Let's grab our headers, clean this up a 'lil bit, I'm going to right-click, copy, go here, right-click, paste as values. We got to make 'em black so we can see 'em. Okay, let's go a little further. We're going to go to columns K and O, highlight those, right-click, unhide because we got more to do. Click here. Rather than sort by name, we want to sort now by hours and sort descending. Equals, sort, open parentheses. Our array, we going to grab this table again. Comma, sort index. We want to sort by the hours, that's the third column. Name region hours, one two three. Put in a three, comma, ascending or descending, we want descending, that means negative one, we can close the parentheses, enter. We got the spill error. This dashed blue line is tellin' us how much room the formula needs to bring back our full result. So we got to make room for it. Let's grab this and drag it over. Let's drag it over here. As soon as we made room, the formula did its thing. We've got our full results. The hours are sorted, largest to smallest. Next, we'd like to sort by region, ascending, and sort by hours descending. So we're going to sort by two criteria. Equals, sort, open parentheses. Our array, we're going to grab this table again. Comma, now in order to sort by multiple criteria, we have to use curly brackets. So, curly bracket. We want to sort by the region first, that's the 2nd column, and then by hours, the third column. So we want two, comma, three. Close the curly brackets. Comma, we need the curly brackets again. We want to sort by region ascending, that's one, comma, by hours descending, negative one. Close the curly brackets. Close parentheses and close the formula, enter. Wow. All of the regions are together, North, South, East, West. And when we look at West, we see Lars worked 40 hours, Janet worked 17. And watch what happens if we go back and we add Denise. And Denise is in the East region, and worked 39 hours, enter. Everything updated. And that's the glory of the SORT function. Sortin' by one criteria or multiple criteria.
Released
3/7/2019- Working with spills
- Using the UNIQUE function
- Sorting with dynamic arrays
- Comparing SORTBY and SORT
- Unstacking records with SEQUENCE
- Creating intelligent filters
- Randomizing data with RANDARRAY
- Combining dynamic arrays in drop lists and more
Share this video
Embed this video
Video: Basic SORT and two criteria