Help - Excel is sortin 10 before 2

Copper Contributor

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

@cheeseontoast101 

 

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

@cheeseontoast101 

 

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.

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.

 

@cheeseontoast101 

 

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.

@cheeseontoast101 

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]

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

@cheeseontoast101 

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

 

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

@cheeseontoast101 

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.

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.

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.

 

Example.PNG

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.