Forum Discussion
SORTBY newb... Other solutions don't appear to apply??
Hello all!
Okay, here is the situation.
I have an Excel spreadsheet and each row has a dropdown that allows you to set the priority of the task. The dropdown has approximately six separate items (Urgent, High, Medium, Low, Complete, Review, etc.).
I set up a formula to change the row color according to the dropdown (previously they were doing it manually) and it works great.
But I also need the entire sheet to sort by the dropdown selection, i.e., it needs to group them by the dropdown selection and order them; all Urgent item rows are at the top of the sheet, High are next, Medium next, etc.
SORTBY seems like it can accomplish this, but every example I have seen is dealing with a small range within the sheet and not sorting the order of entire rows.
The end result I am looking for is to group rows in a specific order according to what was selected in their individual dropdown and sort those rows.
I've seen great examples, but they all seem to just be sorting information in a small range within the sheet and not ordering rows.
Thank you for any suggestions / assistance you can provide and I'm looking forward to learning!
7 Replies
SortBy() takes the source range, sort it based on another array and put result into new place in the same or another sheet. That's practically no limit on the size of the source range.
If to sort in place that could be done programmatically (VBA, OfficeScript) or manually by Data -> Sort. Here you may sort by helper column which map Urgent, High, etc on numerical order of which (e.g. using XLOOKUP); or here you may sort by color if that's more suitable.
So, depends on goals, do you need to sort in place or copy sorted data into another place.
Thank you for your reply, Sergei!
In this instance, I am trying to sort in place as opposed to pushing the data to another place. I had hoped that SortBy could accomplish this in place, but the examples (and your information here) shows me that it can't.
I just want the sheet sorted by dropdown selection in place and I want it to do it dynamically, rather than using the filter sort manually.
Afraid that's VBA or Office if sort with button. If fully dynamically - VBA, but it's not clear what will be the trigger. Plus you won't have the control to sort or not to sort at this very moment.
You can also format it as a table (home -> Format as Table) or just add the quick filter dropdowns (Date -> Filter) and then you can sort or filter the rows and as for colors, you can use conditional formatting (if you didn't already) using custom formula and apply to the whole table and then using something like = $D1="High" where 1 is the first row of the applied to range and $D will 'lock' it to always look at that column and just needs to be set to the correct column
Thanks for your reply, m_tarler!
I did use conditional formatting for the color change and that worked great!
I am exploring the quick filter option; that might be the option I have to use, but I really wanted to have it be dynamic, rather than manual.
