Contributor

# 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 before Reason 2

12 Replies

# Re: Help - Excel is sortin 10 before 2

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

# Re: Help - Excel is sortin 10 before 2

I'm getting a value error
Here is my replication
=TRANSPOSE(SORTBY(UNIQUE(reasons),SUBSTITUTE(Reasons,"Reason 10 ","Reason 2")*1))

# Re: Help - Excel is sortin 10 before 2

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.

# Re: 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.

# Re: Help - Excel is sortin 10 before 2

In beta version of Excel there is the TEXTAFTER function that removes the need for SEARCH/MID.  Singling out the numeric part of the string allows it to be used as the sort criterion

``````= TRANSPOSE(UNIQUE(
SORTBY(reasons,VALUE(TEXTAFTER(reasons, " ")))
))``````

An alternative approach might be to edit the text to start with "Reason 01", so aligning alphabetical and numerical sort.

[As an aside, I always find it strange that spreadsheet column headers are sorted in a manner that conforms neither to alphabetical nor numeric ordering]

# Re: Help - Excel is sortin 10 before 2

Hi
I've replicated this exactly and try to fiddle with it - keep getting the there's a problem with this formula error

# Re: Help - Excel is sortin 10 before 2

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

# Re: Help - Excel is sortin 10 before 2

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

# Re: Help - Excel is sortin 10 before 2

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.

# Re: Help - Excel is sortin 10 before 2

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.

# Re: Help - Excel is sortin 10 before 2

This is what I am trying to create on a visual level. As you can see though it's not in the right order although the reason count for each party is displaying correctly. The 0 after the nine needs to go as well.

# Re: Help - Excel is sortin 10 before 2

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.