(upbeat music)
- 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.
Instead of F4, we're going to type equals, sort.
Open parenthesis, 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.
Want 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,
closed 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 little bit.
I'm going to right click, copy, go here,
right click, paste as values.
We got to make them black so we can see them.
Okay, let's go a little further.
We're going to go to columns K and O,
highlight those, right click, unhide,
because we've got more to do.
Click here.
Rather than sort by name,
we want to sort now by hours as sort descending,
equals, sort, open parentheses.
Our array, we're going to grab this table again,
comma, sort index.
We want to search by the hours.
That's the third column, named region hours.
1, 2, 3,
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 telling 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 it's 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 second column.
And then by ours, the third column.
So we want two, comma three,
close the curly brackets, comma.
We need a curly brackets again.
We want to sort by region ascending, that's one,
comma, by ours 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 Lar's 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.
Sorting by one criteria, or multiple criteria.
All right you all,
let's do some sorting horizontally,
or sort by column.
Here, we host a storytelling show.
We've had 25 shows and here are each of the storytellers,
our performers.
We can see that some people, like Gaston,
has performed in multiple shows.
But now we have a question.
Have the same four people ever been in the same show
together more than once.
That's not easy to answer right now because people are in
shows in different order.
Gaston went second in one show,
he went first in one show,
went last in one show.
Therefore, it would help us if we could get the names
sorted ascending,
and then we can easily compare from show to show,
if the same four people were showed up multiple times.
Let's start here, in cell G3.
Performers, enter,
equals, sort, open parentheses, our array,
we just want to grab this row.
Comma,
We don't need a sort index,
comma, sort order.
We can choose one, for ascending, comma,
sort by column, true.
That's exactly what we need.
Close parentheses, enter.
The names are in order.
All right, let's go back to this cell.
Then drag it down.
Do a visual check.
Yup.
Atticus, Debby, Gaston, Oceane,
all in the proper order.
Next, let's wrap this in a text join
so that all of the names are in one cell.
Go back to the formula.
Equals, text join,
open parentheses, our delimiter,
we don't want a delimiter.
Let's put double quote, double quote,
comma, ignore empty sales. We don't have any.
So let's just double click true, comma.
We don't need to enter the text one, and everything else
because sort, is already saying what we want to join.
So go to the end.
Close parentheses, enter.
Let's go back up.
Drag this back down.
Everything is in one cell.
I'm going to double-click and widen that a little bit.
With it highlighted,
right click, copy.
I'm going to go over here.
Right click, paste as values.
Next, I'm going to go to data, sort ascending.
This is what we wanted.
Visual glimpse shows us,
Atticus, Debby, Gaston, Oceane,
have been in three separate shows together.
Also Carlos, Drew, Jean, and Stephanie,
two different shows together.
We did a lot of work here,
but sort by column was the star of the show,
with text join in a supporting role.
(upbeat music)