Forum Discussion
Help - Excel is sortin 10 before 2
Hi My formula is =transpose(sort(unique(reasons))) The reasons name range is just reason 1, reason 2, reason 3 etc. all the way to 10. Except it's not displaying order - Reason 10 is befor...
I'm getting a value error
Here is my replication
=TRANSPOSE(SORTBY(UNIQUE(reasons),SUBSTITUTE(Reasons,"Reason 10 ","Reason 2")*1))
Here is my replication
=TRANSPOSE(SORTBY(UNIQUE(reasons),SUBSTITUTE(Reasons,"Reason 10 ","Reason 2")*1))
In the Substitute function, why are you substituting Reason 10 with Reason 2?
The formula I proposed assumes that the reason named range contains the values like Reason 1, Reason 2, Reason 3 etc and the substitute function is substituting "Reason " (i.e. Reason followed by a space) to get the numbers in the end and which is used by the SortBy function to sort the values in reason named range by the array of numbers we get from the Substitute function.