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...
That's because it is sorting text values not the numbers.
You may try something like this...
=TRANSPOSE(SORTBY(UNIQUE(reasons),SUBSTITUTE(reasons,"reason ","")*1))
- I'm getting a value error
Here is my replication
=TRANSPOSE(SORTBY(UNIQUE(reasons),SUBSTITUTE(Reasons,"Reason 10 ","Reason 2")*1))Slightly modified formula suggested by Subodh_Tiwari_sktneer - you shall SUBSTITURE on unique values
=LET( u, UNIQUE(Reasons), SORTBY( u,SUBSTITUTE(u,"reason ","")*1) )Forgot TRANSPOSE, but that doesn't matter.
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.
