Thanks. This is a great video for a person like me who almost use sort function every day. The essence lies in using {3,2,1} to sort by columns sequently.
Hi Alan.. great video. SORT and SORTBY sure make things easier than trying to do the same with array formulas in legacy EXCEL. Thanks for the great examples and insights. Thumbs up!!
Thank you, Ram. I don't have a specific video for that, but the curly brackets are for arrays. In this video, they are used to create an array of constant values i.e., {2,1}. You will see curly brackets used whenever Excel returns an array.
This is the video I was looking for! Wow! Quick question if you don't mind, anyone.... How can I just return "The Top 5" in a sorted list of values? (doing Baseball stats, and want to return the Top 5 in Hits, and their names). Thanks in advance.
Hi. Nice vid. Inspirational as always. Is the "row" argument intended to assist the new SORT/SORTBY functions in the case two SUMIFS'D products have the same total/count? I wondered that as soon as I saw you paste in the little bit to find the array and sort the product names base on the SUMIFS piece.
Thank you, David. The row argument is asking if you want to sort by columns or rows. We are sorting the rows, but you could set that to true to sort the columns, which is very rare.
@@Computergaga Thanks for replying, :). Ah, I see. Makes me wonder. Perhaps some people would in those rare cases sort by columns with the intent to impact the data story of charts. Say, a header might be names or places, but a "header-header", if you will, might be a date or a manual number to force presentation. Cheers!
Love your videos………………………. I would like SORT to be selectable so that I can change the COLUMN that is sorted from a DROP-DOWN selection. I have got the following working where you select a NUMBER from a DROPDOWN, and it then works in the formula if you use INDIRECT - The problem is this formula is not DYNAMIC. Any suggestions to make a DYNAMIC selectable sort on COLUMN drop down =SORT(B11:D23,INDIRECT("G8")) this works but is not dynamic G8 is a dropdown 1,2,3 for columns B,C,D
Why did you not just use SUMIFS in column I - With SPILL# - =SUMIFS(x:x,x:x,H1#) to get the totals for each unique value or was this just to demonstrate SORTBY getting the data ?
I am using Excel 2010 and I have a question regarding sorting with multiple values in a cell... I have a database where participants checked off boxes to a questionnaire. So now I have a cell with multiple values like 1 or 2,10 or 1,2 or 1,2,10 or 1,5 the numbers represent a person's choice or choices.... how would I sort let's say I wanted everyone who chose choice #1?
Thank you for the video! I’ve been trying to sort referenced cells for ages whilst (keeping the references intact) and it’s been ordering them by the function not the value of the referenced data! Do you know how I can use this function to sort discontiguous arrays? Or how in functions I can make it apply to a discontiguous range? E.g: the array would be cells a2:a17 and also cells d:2:17 but I must neglect columns b and c. I’ve tried using a2:a17&d2:d17 as the array, using names ranges, and using indirect () functions and it doesn’t work!
Hi, yes you can do this with CHOOSE. The following formula extracts only ranges D2:A17 and D2:D17. It sorts the array by column 2 (D2:D17) in a descending order. =SORT(CHOOSE({1,2},A2:A17,D2:D17),2,-1)
What is i want to order by Total demand Confirmed PO Cumulative Delta In that order each time for each part number I have for materials? When I sort it puts it out on the order I have it in. Can anyone help?
It is simply amazing to see, how Excel has made things so easy for all of us. Fantastic video Alan !!!
Thank you very much, Sachin. Long may it continue.
Thanks. This is a great video for a person like me who almost use sort function every day. The essence lies in using {3,2,1} to sort by columns sequently.
Thanks Mark. Much appreciated.
One of the most valuable Excel tutorial videos I've ever came across with. Much obliged sir!
You're very welcome! Thank you so much.
Thank you for this video. I especially liked the advanced sorting using the arrays. Much appreciated.
You are welcome, Paul. Thank you.
The dynamic array is just simply AMAZING!
You're right there Yu Sun. I love them 😍
This was the exact video I was looking for! For days! Finally found it! Thanks a ton! :)
Excellent! You're very welcome.
@@Computergaga Subbed! :)
ua-cam.com/users/shortsGWNhDXi7Am4?sub_confirmation=1
Short vid on how to use sort function
Thank you , for that GRANDDD class !!!!..
You're very welcome. Thank you.
Hi Alan.. great video. SORT and SORTBY sure make things easier than trying to do the same with array formulas in legacy EXCEL. Thanks for the great examples and insights. Thumbs up!!
Thank you Wayne. They sure do.
ua-cam.com/users/shortsGWNhDXi7Am4?sub_confirmation=1
Short vid on how to use sort function
Great Tutorial..These New Functions Certainly Make Life Easier!Thank You Alan :-)
Yes, they are awesome. Thank you Darryl.
An excellent explanation.
Thank you, Alan
Thank you, Meni.
ua-cam.com/users/shortsGWNhDXi7Am4?sub_confirmation=1
Short vid on how to use sort function
Sorting an array.. with Alan Murray! Yay! Yay!
😊 👍
brilliant! Just what I need today with my report :D
Great! Thank you Marc.
Great video, do you have a video that explain the use of curly brackets in general? Thanks,
Thank you, Ram. I don't have a specific video for that, but the curly brackets are for arrays. In this video, they are used to create an array of constant values i.e., {2,1}. You will see curly brackets used whenever Excel returns an array.
This is the video I was looking for! Wow! Quick question if you don't mind, anyone.... How can I just return "The Top 5" in a sorted list of values? (doing Baseball stats, and want to return the Top 5 in Hits, and their names). Thanks in advance.
Great! Thank you, Brandan. I have a video on doing that here - ua-cam.com/video/sk3z8e2mvng/v-deo.html
Thank you
You're welcome
I can't call "issues" function on my worksheet, how did you do that?
Thank you Alan!
You're welcome. Thank you, Luciano.
nice video. btw, how it can be realized as a table when you click the column?
Hi. Nice vid. Inspirational as always.
Is the "row" argument intended to assist the new SORT/SORTBY functions in the case two SUMIFS'D products have the same total/count?
I wondered that as soon as I saw you paste in the little bit to find the array and sort the product names base on the SUMIFS piece.
Thank you, David. The row argument is asking if you want to sort by columns or rows. We are sorting the rows, but you could set that to true to sort the columns, which is very rare.
@@Computergaga
Thanks for replying, :).
Ah, I see.
Makes me wonder. Perhaps some people would in those rare cases sort by columns with the intent to impact the data story of charts. Say, a header might be names or places, but a "header-header", if you will, might be a date or a manual number to force presentation.
Cheers!
Great sir amazing
Thank you 😊
Is there an old school method for sortby function for previous excel version
Excellent sir
Thank you.
Excellent tutorial! Do you have a video for sorting alphanumeric data? For example AA-1, AB-1, AC-1, AA-12, AB-13, AC-14...etc.
Thanks.
Thank you. No, not on alphanumeric. Excel uses Custom Lists for text data outside of the typical A-Z, but that sounds awkward if you have many items.
I am crazy about this channel
😜
Thank you sir
You're welcome, Rahul.
Love your videos……………………….
I would like SORT to be selectable so that I can change the COLUMN that is sorted from a DROP-DOWN selection.
I have got the following working where you select a NUMBER from a DROPDOWN, and it then works in the formula if you use INDIRECT - The problem is this formula is not DYNAMIC. Any suggestions to make a DYNAMIC selectable sort on COLUMN drop down
=SORT(B11:D23,INDIRECT("G8")) this works but is not dynamic G8 is a dropdown 1,2,3 for columns B,C,D
Thanks sir
You're welcome.
Why did you not just use SUMIFS in column I - With SPILL# - =SUMIFS(x:x,x:x,H1#) to get the totals for each unique value or was this just to demonstrate SORTBY getting the data ?
Amazing video..sir.. kindly share the link for the excel practice sheet...!
Thank you very much, Neha. You can download the file here - www.computergaga.com/_excel/files/365-functions/sort-and-sortby.xlsx
@@Computergaga Unfortunately, this link also does not provide the practice file.
I am using Excel 2010 and I have a question regarding sorting with multiple values in a cell... I have a database where participants checked off boxes to a questionnaire. So now I have a cell with multiple values like 1 or 2,10 or 1,2 or 1,2,10 or 1,5 the numbers represent a person's choice or choices.... how would I sort let's say I wanted everyone who chose choice #1?
Thank you for the video! I’ve been trying to sort referenced cells for ages whilst (keeping the references intact) and it’s been ordering them by the function not the value of the referenced data!
Do you know how I can use this function to sort discontiguous arrays? Or how in functions I can make it apply to a discontiguous range? E.g: the array would be cells a2:a17 and also cells d:2:17 but I must neglect columns b and c. I’ve tried using a2:a17&d2:d17 as the array, using names ranges, and using indirect () functions and it doesn’t work!
Hi, yes you can do this with CHOOSE. The following formula extracts only ranges D2:A17 and D2:D17. It sorts the array by column 2 (D2:D17) in a descending order.
=SORT(CHOOSE({1,2},A2:A17,D2:D17),2,-1)
What is i want to order by
Total demand
Confirmed PO
Cumulative Delta
In that order each time for each part number I have for materials? When I sort it puts it out on the order I have it in. Can anyone help?
This question is beyond the scope of this comment box. The responder would need the data and more detail.
@@Computergaga I figured it out. Thanks for the response.
👍👍👍👍👍am trying to download the file and it say Not found, error 404?!
This has been fixed, Redha. Sorry, about that.
❤
😊
pro tip: put him on 1.25x speed
😯
😅
Explanation not enough
Not enough?
VERY POOR EXPLANATION. I CANT UNDERSTAND
My apologies