trouble with sort

[orsm6]

Hi all - I am trying to sort 2 columns of source data but it doesn't sort it as i need it to.

I am trying to build a sporting results 'ladder' and my sort formula is almost there, but not quite.
it will sort the first number correctly.

but if the first number is 0 then the sorted numbers are wrong, hopefully the screenshot will help.

how can I change my sort formula to also include the numbers after the '-'?: =SORT(AF4:AG44,2,-1)

 

[Momentman]

One funky way is to replace the hyphen with nothing so 2-32 becomes 232 and 0-7 becomes 07, so that they can be effectively numbers and the sort should work

=SORTBY(AF4:AG44,SUBSTITUTE(AG4:AG44,"-","")*1,-1)

 

[orsm6]

One funky way is to replace the hyphen with nothing so 2-32 becomes 232 and 0-7 becomes 07, so that they can be effectively numbers and the sort should work

=SORTBY(AF4:AG44,SUBSTITUTE(AG4:AG44,"-","")*1,-1)

Hi Momentman, thanks for your help. OK i thought this didn't work... but i have a space before and after hyphen.

A new issue has come up... the range of source list has blanks, so the sorted list returns all the blanks as a bunch of zeros, how can they be excluded from the sorted list? see snip of result

 

[Peter_SSs]

Try ..

23 10 18.xlsm
	AF	AG	AH	AI	AJ
4	a	2 - 32		d	2 - 99
5	g	0 - 14		a	2 - 32
6	b	1 - 5		x	1 - 15
7	d	2 - 99		b	1 - 5
8	x	1 - 15		g	0 - 14
9
orsm6

Cell Formulas
Range	Formula
AI4:AJ8	AI4	=LET(f,FILTER(AF4:AG44,AG4:AG44<>""),SORTBY(f,SUBSTITUTE(TAKE(f,,-1)," - ","")*1,-1))
Dynamic array formulas.

 

[orsm6]

One funky way is to replace the hyphen with nothing so 2-32 becomes 232 and 0-7 becomes 07, so that they can be effectively numbers and the sort should work

=SORTBY(AF4:AG44,SUBSTITUTE(AG4:AG44,"-","")*1,-1)

Try ..

23 10 18.xlsm
	AF	AG	AH	AI	AJ
4	a	2 - 32		d	2 - 99
5	g	0 - 14		a	2 - 32
6	b	1 - 5		x	1 - 15
7	d	2 - 99		b	1 - 5
8	x	1 - 15		g	0 - 14
9
orsm6

Cell Formulas
Range	Formula
AI4:AJ8	AI4	=LET(f,FILTER(AF4:AG44,AG4:AG44<>""),SORTBY(f,SUBSTITUTE(TAKE(f,,-1)," - ","")*1,-1))
Dynamic array formulas.

thanks again Peter - the skills of you guys is impressive. this got me out of a bind.

 

[Peter_SSs]

thanks again Peter

You're welcome again.

 

[orsm6]

You're welcome again.

Hi Peter - sorry if this is incorrect to do, but something weird is going on with my ladder results. It doesn't seem to prioritise the first number then the number after the hyphen, you can see from my snip that the result 6 - 115 is in 3rd place but should be in 6th....

the result is from the formula that you supported me with in the above thread

are you able to help?

 

[Peter_SSs]

It doesn't seem to prioritise the first number then the number after the hyphen,

I don't believe that we had that specific requirement stated previously so I guess not too surprising that it wasn't implemented.

Also, are we still trying to sort two columns?
See if this sort of structure helps.

23 11 07.xlsm
	A	B	C	D	E
1
2	a	8 - 410		c	8 - 503
3	b	4 - 23		a	8 - 410
4	c	8 - 503		g	8 - 89
5	d	6 - 60		e	6 - 115
6	e	6 - 115		d	6 - 60
7	f	0 - 60		b	4 - 23
8	g	8 - 89		f	0 - 60
9
10
Sort

Cell Formulas
Range	Formula
D2:E8	D2	=LET(f,FILTER(A2:B10,B2:B10<>""),r,TAKE(f,,-1),SORTBY(f,10^6*TEXTBEFORE(r,"-")+TEXTAFTER(r,"-"),-1))
Dynamic array formulas.

 

[orsm6]

I don't believe that we had that specific requirement stated previously so I guess not too surprising that it wasn't implemented.

Also, are we still trying to sort two columns?
See if this sort of structure helps.

23 11 07.xlsm
	A	B	C	D	E
1
2	a	8 - 410		c	8 - 503
3	b	4 - 23		a	8 - 410
4	c	8 - 503		g	8 - 89
5	d	6 - 60		e	6 - 115
6	e	6 - 115		d	6 - 60
7	f	0 - 60		b	4 - 23
8	g	8 - 89		f	0 - 60
9
10
Sort

Cell Formulas
Range	Formula
D2:E8	D2	=LET(f,FILTER(A2:B10,B2:B10<>""),r,TAKE(f,,-1),SORTBY(f,10^6*TEXTBEFORE(r,"-")+TEXTAFTER(r,"-"),-1))
Dynamic array formulas.

Hi Peter - apologies the initial request wasn't clear. Will keep that in mind for the future

but as it stands, this works great.

thanks again again :D

 

[Peter_SSs]

Cheers. Thanks for the confirmation.