This part 2 on a series on Dynamic Arrays in Excel. These formulas were announced at Ignite on September 24, 2018.
This video covers SORT and SORTBY.
Topics in the video:
Sort Excel data with a formula
Use SORT to sort a range
Use SORTBY to sort one column by another column
Use SORTBY with RANDARRAY to do random drug testing or random without repeats.
To use the file from this episode: https://wwww.mrexcel.com/download-c...ort-with-a-formula-using-sort-and-sortby.xlsx
This video covers SORT and SORTBY.
Topics in the video:
Sort Excel data with a formula
Use SORT to sort a range
Use SORTBY to sort one column by another column
Use SORTBY with RANDARRAY to do random drug testing or random without repeats.
To use the file from this episode: https://wwww.mrexcel.com/download-c...ort-with-a-formula-using-sort-and-sortby.xlsx
Transcript of the video:
Learn Excel from MrExcel podcast episode 2234. SORT and SORTBY Functions in Excel.
All this week, we're talking about the new modern ARRAY functions in Excel.
I have a nice little data set over there in A3 to C16. I would like to sort that data with a formula because these numbers are going to change.
Can't trust my manager's manager to know how to sort, so over here in the output range =SORT.
Check this out, a brand new SORT function.
Here's my ARRAY, I can include the headers here comma What's the SORT index? This is column 1, column 2, column 3 of the array.
So SORT index is 3, I want to sort by 3 and then ascending or descending - 1 for descending and we're almost done. What's the by column? Alright.
Now this is really obscure but Excel usually sorts the rows but it can also sort the columns, data, sort more options, sort, left to right, so if you're doing one of those weird sorts, they have that fourth function for you.
Most people will never do one of those.
And bam! There's our results and those results are live, so we'll make this a little bit wider here.
Let's take Bernardo 291...
Bernardo 291, we're gonna choose Bernardo up to 999 999 And bam! The data re-sorts on the fly. The SORT function great for single level sorts.
You can't sort by color.
You can't sort by cell icon.
But what if we need to do a two level sort?
So first we have to sort by team ascending and then score descending.
Now in this one I'm going to have to copy the headers over because I haven't figured out the headers sort with the data.
And this next function is called equal SORTBY. This is awesome. Here's our array. Leave those headers out.
Do I have to press F4? No, because this is a single formula in a single cell. There's no need to press F4.
By_array is sort by column B, and we want that to be ascending, so 1, then sort by score and we want that to be descending so -1.
Let me just press a comma.
Yes, it keeps going, you can add more and more and more. But I only have two levels to sort today.
Press ENTER Wow. Did that work?
Yes, it worked. Do we need to return all of the columns?
No, we do not.
If I just want the name, I can say that I want to return just column A and sort by something that's outside of the range.
So we're sorting A by B then by C, press ENTER and we get the exact same results that we have here without having to show the other columns. It sorts by those other columns, but we don't have to show those items.
How amazingly cool is that! Got to be on Insider fast if you're on the monthly channel or the semi-annual channel.
Well, yeah, these will come out, you know, hopefully by the beginning of 2019.
Check out my new book.
Microsoft Excel 2019: Inside-out. Click the i on the top right hand corner for more information about this book.
Hey, I want to thank you for stopping by.
We will see you next time for another netcast from MrExcel
All this week, we're talking about the new modern ARRAY functions in Excel.
I have a nice little data set over there in A3 to C16. I would like to sort that data with a formula because these numbers are going to change.
Can't trust my manager's manager to know how to sort, so over here in the output range =SORT.
Check this out, a brand new SORT function.
Here's my ARRAY, I can include the headers here comma What's the SORT index? This is column 1, column 2, column 3 of the array.
So SORT index is 3, I want to sort by 3 and then ascending or descending - 1 for descending and we're almost done. What's the by column? Alright.
Now this is really obscure but Excel usually sorts the rows but it can also sort the columns, data, sort more options, sort, left to right, so if you're doing one of those weird sorts, they have that fourth function for you.
Most people will never do one of those.
And bam! There's our results and those results are live, so we'll make this a little bit wider here.
Let's take Bernardo 291...
Bernardo 291, we're gonna choose Bernardo up to 999 999 And bam! The data re-sorts on the fly. The SORT function great for single level sorts.
You can't sort by color.
You can't sort by cell icon.
But what if we need to do a two level sort?
So first we have to sort by team ascending and then score descending.
Now in this one I'm going to have to copy the headers over because I haven't figured out the headers sort with the data.
And this next function is called equal SORTBY. This is awesome. Here's our array. Leave those headers out.
Do I have to press F4? No, because this is a single formula in a single cell. There's no need to press F4.
By_array is sort by column B, and we want that to be ascending, so 1, then sort by score and we want that to be descending so -1.
Let me just press a comma.
Yes, it keeps going, you can add more and more and more. But I only have two levels to sort today.
Press ENTER Wow. Did that work?
Yes, it worked. Do we need to return all of the columns?
No, we do not.
If I just want the name, I can say that I want to return just column A and sort by something that's outside of the range.
So we're sorting A by B then by C, press ENTER and we get the exact same results that we have here without having to show the other columns. It sorts by those other columns, but we don't have to show those items.
How amazingly cool is that! Got to be on Insider fast if you're on the monthly channel or the semi-annual channel.
Well, yeah, these will come out, you know, hopefully by the beginning of 2019.
Check out my new book.
Microsoft Excel 2019: Inside-out. Click the i on the top right hand corner for more information about this book.
Hey, I want to thank you for stopping by.
We will see you next time for another netcast from MrExcel
