Highest And Lowest Score Order

Hi,

Can anyone help with this highest and lowest score order. As I just can't get them in the order that I want. The formulas that I'm using just don't work, when there is duplicated numbers.




this is the order I want
Highest Score Name Week Name Week
1 22 Mick Week 12 Mick Week 12
2 21 Albert Week 13 Albert Week 13
3 20 Steve Week 12 John Week 8
4 20 John Week 9 Bish Week 9
5 20 Bish Week 8 Steve week 12
6 19 Albert Week 13 Ablert Week 10
7 19 Mick Week 12 Bish Week 12
8 19 Bish Week 10 Mick Week 13
9 18 Bish Week 10 Bish Week 8
10 18 Bish Week 8 Bish Week 10

Sorry if this is not very clear!

Please see the attached file

Highest and Lowest Score Order.xlsx

Hi,

The quickest way is to transpose the data, since Excel's priority is to process row by row.

But if you still want to keep your data that way, then these are the array formulas :

I8 :

=INDEX($20:$20,SMALL(IF($C$21:$O$26=$C8,COLUMN($C$21:$O$21)),COUNTIF($C$7:$C8,C8)))

H8 :

=INDEX(B:B,SMALL(IF(INDEX($A$21:$O$26,0,MATCH($I8,$20:$20,0))=$C8,ROW($A$21:$A$26)),COUNTIFS($C$7:$C8,$C8,$I$7:$I8,$I8))

)

S8 :

=INDEX($20:$20,SMALL(IF($C$21:$O$26=$M8,COLUMN($C$21:$O$21)),COUNTIF($M$7:$M8,M8)))

R8 :

=INDEX(B:B,SMALL(IF(INDEX($A$21:$O$26,0,MATCH($S8,$20:$20,0))=$M8,ROW($A$21:$A$26)),COUNTIFS($M$7:$M8,$M8,$S$7:$S8,$S8)))

Regards

see the attached file

You are welcome and thanks for the reputation point.

Regards