Forum Discussion
Help - Excel is sortin 10 before 2
Hi. To sort them by number values try this one.
Reasons is a name of the combination of Reason 1;...; Reason 10
=TRANSPOSE(SORT(VALUE(MID(UNIQUE(Reasons);8;100))))
Note: 8 in the formula is the start number of MID function. It means length of "Reason " +1 to catch the start of your numbers.
100 in the formula is the number of characters of MID function to after the start number. i thought that 100 characters are enough to catch numbers of reasons.
I put an excel file as well to be clearer for you. Do not forget to put a name before you try this formula or choose range instead of adding a name instead of "Reasons" in the formula.
I've replicated this exactly and try to fiddle with it - keep getting the there's a problem with this formula error
- Hi there. i put an excel file for you in my answer. Check it if you have a problem. Probably you tried it before you create a name 🙂
- Didn't work sorry - Data that was then supposed to fall under reason 10 would fall under reason 2, reason 3 data fell under reason 4 etc. Thanks for you help though.
- I am not sure you checked the document which i shared in my answer or not. For this kind of situations you can share the document which you have a problem. That can be easier to get help.
It may just be a localisation thing. Do you use "," or ";" as a parameter separator?
Mind you, I also had to reinstate the "Reason" text to obtain the intended result= "Reason " & TRANSPOSE(SORT(VALUE(MID(UNIQUE(reasons),8,100))))
