May 17 2022 06:02 PM
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
May 17 2022 06:25 PM
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))
May 17 2022 08:26 PM
May 17 2022 11:23 PM
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.
May 17 2022 11:51 PM - edited May 18 2022 01:39 AM
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.
May 18 2022 12:05 AM
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]
May 18 2022 12:33 AM
May 18 2022 01:05 AM
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))))
May 18 2022 01:25 AM
May 18 2022 01:43 AM
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.
May 18 2022 04:32 PM
May 18 2022 04:47 PM
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.
May 19 2022 03:46 AM