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))
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.