• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sort multiple rows of Numbers Largest to smallest on all rows

Colin Brad

New Member
Hi I have just joined a photo federation and they have a scoring sheet that needs ranking without equal places, if there is a tie their criteria is, take the highest and lowest scores and add together, if there is still a tie take the second highest and second lowest and add and so on until one club comes out on top,
my solution as I see it is to sort the rows highest to lowest and then it is a simple sum to do the rest, in the spreadsheet attached you will see the rows highlighted in yellow are tied, but it could be any of the rows so ideally sort all rows highest to lowest I have tried the excel functions with no joy and my VBA is very rusty, thanks in anticipation
 

Attachments

  • Eaf tie calc.xlsx
    10.6 KB · Views: 3
Not a vba solution in the attached but…
A formula in cell AC2:
Code:
=LET(res,C2:AA2,seq,SEQUENCE(,COLUMNS(res)/2),LARGE(res,seq)+SMALL(res,seq))
which can be copied down.
This formula spills to the right. I've added headers in row 1 to give an idea o what each column represents:
H+L_1 is the 1st highest + 1st lowest scores added, H+L_2 is the 2nd highest + 2nd lowest scores added, etc.
Now you need to select the whole range A1:AN12 and sort as follows (but add more columns to the sort):

1739809089578.png

I've done this sort in the attached but haven't added all the12 columns to the sort. [I've tweaked rows 9 and 10 on the sheet to be very similar where a tie is untied only at the 3rd highest+3rd lowest level, for testing/demonstration]
After sorting, you can delete the extra columns and add an index column to show the rank.
Coding this in vba wouldn't be straightforward; I wouldn't look forward to writing such a user-defined function.
I'll try to do this in Power Query too, but later.
 

Attachments

  • Chandoo58273Eaf tie calc.xlsx
    12.4 KB · Views: 1
Power Query solution in the attached.
Note only raw data in the source table on the left. Play with adjusting the numbers in that table (I played with the red highlighted ones) then right-click somewhere on the right hand table and chose Refresh to get an updated, sorted snfd rsnked results table with totals. It uses similar logic to the formula solution but hidden from view.
 

Attachments

  • Chandoo58273Eaf tie calcPQ.xlsx
    20.5 KB · Views: 2
Not a vba solution in the attached but…
A formula in cell AC2:
Code:
=LET(res,C2:AA2,seq,SEQUENCE(,COLUMNS(res)/2),LARGE(res,seq)+SMALL(res,seq))
which can be copied down.
This formula spills to the right. I've added headers in row 1 to give an idea o what each column represents:
H+L_1 is the 1st highest + 1st lowest scores added, H+L_2 is the 2nd highest + 2nd lowest scores added, etc.
Now you need to select the whole range A1:AN12 and sort as follows (but add more columns to the sort):

View attachment 89739

I've done this sort in the attached but haven't added all the12 columns to the sort. [I've tweaked rows 9 and 10 on the sheet to be very similar where a tie is untied only at the 3rd highest+3rd lowest level, for testing/demonstration]
After sorting, you can delete the extra columns and add an index column to show the rank.
Coding this in vba wouldn't be straightforward; I wouldn't look forward to writing such a user-defined function.
I'll try to do this in Power Query too, but later.
Hi Thanks you very much this one works great.
 
Back
Top