sort help

[orsm6]

Hi all - trying to sort the ladder results of a comp i run.

could you please tell me how i can sort range M5:O200.
M - contains player name
N - their win points
O - their % points

I would like to sort it by column N high to low, then column O high to low.

so if 2 players share the same win points, their % points would then need to be highest to lowest..... hope this made sense.

 

[dreid1011]

Have you tried the built-in Custom Sort feature?

You can sort by each column in whichever order you choose.

 

[orsm6]

Have you tried the built-in Custom Sort feature?

View attachment 119767

You can sort by each column in whichever order you choose.

View attachment 119768

sorry - needs to be by formula

 

[Alex Blakenburg]

A formula will put the output somewhere else but if that is what you want then try:

=SORTBY($M$5:$O$100,$N$5:$N$100,-1,$O$5:$O$100,-1)

If you use a table to store the data then you are not dependent on hard coding the data range.

If you can't use a table the below will reduce the amount of maintenance required.

=LET(rng,$M$5:$O$200,
     SORTBY(rng,CHOOSECOLS(rng,2),-1,CHOOSECOLS(rng,3),-1))

 

[orsm6]

A formula will put the output somewhere else but if that is what you want then try:

=SORTBY($M$5:$O$100,$N$5:$N$100,-1,$O$5:$O$100,-1)

If you use a table to store the data then you are not dependent on hard coding the data range.

If you can't use a table the below will reduce the amount of maintenance required.

=LET(rng,$M$5:$O$200,
     SORTBY(rng,CHOOSECOLS(rng,2),-1,CHOOSECOLS(rng,3),-1))

thanks heaps for your help. yeah was aware there would be another ouput, this is OK as the ladder is on another sheet to where the raw data is. I got it going.

 

[Alex Blakenburg]

Appreciate the feedback. Glad we could help.

 

[Fluff]

Another option would be

=SORT(M5:O200,{2,3},-1)

 

[orsm6]

Another option would be

=SORT(M5:O200,{2,3},-1)

Thank you Fluff - while both work, this would provide an easier solution for some of us who aren't as savvy.

 

[Fluff]

Glad we could help & thanks for the feedback.