VBA/Macros - sorting across multiple repetitive columns

Good day. Hopefully someone can help with this? I have attached an example spreadsheet with two tabs. The first tab (Matrix) shows raw data scores - there are 28 measures in Column A and Participants in Row 1. There are always 28 measures but participants can vary. What I need to happen is to sort each measure in a Lineup tab i.e. rank all the participants listed in the Matrix tab from lowest score to highest score for each of the 28 measures. Obviously this can be done manually but a VBA/macro solution will be so much faster and easier. Unfortunately I have to keep the data in this format so the macro will need to transpose the data to get into the formt in the Lineup tab. Thanks for you assistance.

test.xlsx

See next code

Please Login or Register  to view this content.

Sorry for the delay in getting back to you. This looks very good and works well with this data set. However, I sometimes have to use quite large data sets i.e. over 1,000 participants. Is it possible to design the code to take into account such a large number of participants? I think currently this code only analyses 15 participants which is probably due to fact that the transpose element of the code uses Columns P:AT. Could we solve this by creating another tab e.g. Transpose where this data can be copied to instead of using the Matrix tab?

Again very impressive. But still hasn't solved the additional problem when I have additional participants (which can be upto 500 and beyond) . I get the following message "Run Time Error "9" Subscript out of Range" when I have data in column AE and beyond (30 participants). Data upto column AD (29 participants seems to work fine).

test (2).xlsm

Try

Please Login or Register  to view this content.

Brilliant. Thanks both for all your help with this. Both solutions work well now. However, xladept's solution appears to be much faster. I will do some more testing with bigger datasets but believe you have both cracked this.

xladept's solution is clearly faster.
If there is still speed issue some others possibilities exist but a bit longer to prepare