Transposing a single column to 3 or 4 columns based on ranking

[Ian McPherson]

Valued service providers.
I request some help in transposing a single column of players to 3 (or 4) columns based on the score (rank) of the individual players, refer the attached example.
Columns B and C list a number of players and their scores. They need to be transposed to columns F, G and H based on their rank and in the cell order shown. If there are an odd number of players the 1st group may end up as a single or 2 man group shown in the example.
To add flexibility, the number of players could be up to 150 and the groups could be 3 or 4 man groups.
I suspect this can be easily solved with a macro, which unfortunately I am not familiar with.
Thanking you in advance.

[natefarm]

Give this a shot. First enter the number of players-per-group in cell F1 (adjust as needed).

Please Login or Register  to view this content.

[Ian McPherson]

Thanks natefarm for the prompt feedback. It works OK but the players need to be in the reverse order as per my original example, i.e. the top ranked player in cell I12 and the worst ranked player in cell G4, with the remainder in the order shown.
If there are an odd number of players the 1st group may end up as a single or 2 man group shown in the example.

[natefarm]

Ok, try this. It sorts the players in reverse order, builds the groups, then sorts it back.

Please Login or Register  to view this content.

[Ian McPherson]

The order looks correct, although not in player# numerical order. However, the seeded draw for a golf comp (say) would have the top three players (group 9) teeing off last, following in descending rank until the worst scores (players) teeing off first (group 1) as shown below. Sorry to be a pain!

Time Group
8:00 1 Player 26 Player 25
8:08 2 Player 24 Player 23 Player 22
8:16 3 Player 21 Player 20 Player 19
8:24 4 Player 18 Player 17 Player 16
8:32 5 Player 15 Player 14 Player 13
8:40 6 Player 12 Player 11 Player 10
8:48 7 Player 9 Player 8 Player 7
8:56 8 Player 6 Player 5 Player 4
9:04 9 Player 3 Player 2 Player 1

[natefarm]

Ok, new approach. Sorry about all the do-overs.

Please Login or Register  to view this content.

[Ian McPherson]

Tantalisingly close! Can you achieve the order shown above, i.e. player1 in the RH column moving numerically right to left etc. One more shot?

[jindon]

This should do...

[Ian McPherson]

That is the correct order jindon. However it breaks down for (say) 28 players. Could you remove the run "button" and the time slots (since the latter may vary) and also add a cell input on the sheet for
2, 3 or 4 column player groups.
Cheers!

[jindon]

Don't understand how you want it.

Perhaps, someone else can help you.

[Ian McPherson]

Thanks for your perserverence!

[natefarm]

Here ya go.

Please Login or Register  to view this content.

[Ian McPherson]

That's it natefarm! Good work. I had given up and was about to repost on Commercial. Feel free to take the $$posted.