Original subtitles

It's time now to do exercise three, where we're going to practice some of the skills that we've learned

in this section on sorting and filtering.

So there are a couple of different things I'd like you to do here.

Once again, we have on the left hand side, just a small table.

It shows me some countries listed out the regions those countries belong to, the revenue and the profit

that they've generated.

And the first thing I'd like you to do here is I'd like you to create a unique list of all of the regions,

and I'd like you to do that using an excel function.

And then once you have that unique list, I'd like you to use that list to create a data validation

dropdown list and sell f four.

And then finally, if we cast our eyes over to the right hand side notice that I have the same column

headings running across the top.

I'd like you to create a filter that filters results by the region selected cell f full.

And I'd like you to include a meaningful message so that if there are no records, we don't get an error.

We get a message that says no records.

So to give you a little bit of an idea as to which direction to go in, you're going to need to be using

the unique function and also the filter function to complete this exercise.

And then the second part of this exercise is sorting data.

So once again, we have similar information, and I want you to use the sort by function to sort the

athletes by root and position in ascending order.

So now is the time to pause the video, give the exercise a go.

And if you get stuck or you'd like to see my answer, then please keep watching.

So the first thing we need to do here is we need to create a unique list of the regions.

So to do this, I'm going to kind of go over here.

So.

So I'm in a blank space and I'm going to use the unique function now for this, or we need to do is

to select the range.

So we want a unique list of the regions.

So a control shift down arrow.

I'm working up in the formula bar now.

Close the bracket, enter and there is my unique list.

So now I want to use this unique list to create a data validation dropdown list in cell full.

So let's clicking seller for up to data into data validation.

We're going to create a list and the source for our list is going to be our unique list in column and

click on.

OK.

And now I have my little dropdown arrow and I can select my different regions.

Remember, if you don't want these visible in Column M, you can simply right click and hide that column.

Now, the second part of this exercise was to create a filter that filters the results by the region

selected in f four.

And I wanted you to add a meaningful message if there are no records.

So what we're going to do here is we're going to use the filter function now for this, our array is

going to be everything that we have in this table.

And then we need to tell the function what it is we want to include.

So I basically want to include all the records where the region is Australasia, so I'm going to include

the region.

When it's equal to whatever we have in Cell F full coma, what do I want it to do if it's empty?

Well, I want to add a message that says no records and close the bracket.

Let's enter.

And would you take a look at that is picked out?

All of those results from the table?

And because this is dynamic, if I change the region to Europe, I'm going to get an updated list of

results.

The second part of this exercise was to sort our data, and I want you to use the salt by function to

do this, and I'm going to salt the athletes by their root and position in ascending order.

So once again in Sochi, we're going to use the salt by formula.

My array will.

This is going to be all of my data.

So let's select everything that we have in here.

I'm going to work up in that formula bar comma.

What do I want to sort by?

Well, I want to sort by two things.

I want to sort by the root and position in ascending order so that by array, one argument is going

to be the root.

So let's select.

Everything that we have to say, that comma sort order one will I want to saw in ascending order?

So we're going to add a one just there comma by array.

So now I want to soar by the position, so we're going to select the position column comma and I want

to soar by ascending order again.

So a one on the end that close the bracket ends.

And would you take a look at that?

We're now sorting by route, first of all, in alphabetical order.

So I have Islip first and then West Slope and then we're sorting by the position.