Sorting formulated values for ranking

Hi,

I am looking to compile a sorting / ranking table. I have completed the main table but want to transpose the data to an easy to read ranking table. When using the TRANSPOSE function it does not carry the data the way I intend it and using the SORT table options does not extend the data.

The ideal table would look like the yellow table in the second sheet.

Any pointers would be a great help!

Thanks, ChemistryBMS

Maybe... X3:
=INDEX($46:$46,AGGREGATE(15,6,COLUMN($C$46:$Q$46)/($C$43:$Q$43=Z3),COUNTIF(Z$3:Z3,Z3)))

Y3:
=INDEX($42:$42,AGGREGATE(15,6,COLUMN($C$46:$Q$46)/($C$43:$Q$43=Z3),1))

Z3:
=AGGREGATE(15,6,$C$43:$Q$43,ROWS(Z$3:Z3))

One ore option.
In X2

=IFERROR(INDEX($C$22:$Q$22,AGGREGATE(14,6, COLUMN($C$22:$Q$22)/($C$42:$Q$42=Y3),COUNTIF($Y$3:$Y3,$Y3))-COLUMN($B$22)),"")

In Y2

=LARGE($C$42:$Q$42,ROW(A1))

In Z2

=RANK(Y3,$Y$3:$Y$17)

I also tried to find a solution without an auxiliary table, using SORTBY.

But then I run into 2 problems:
The SORTBY formula don't have a sort by column option. So I have to transpose the table first.
But after that I can no longer refer to the sort column of the transposed table, because you have to specify a range for that.

Or maybe there is another useful way to eliminate the help table in post #4.

I look forward to the ideas of the Excel experts.