I have 3 columns/lists of players [Skips; 2nds; 3rds] and I want to randomnly sort these into teams [of three players: skips; 2nd; 3rd] I don't want to sort each column alphabetically - I want to randomnly sort each column into teams. Can anyone tell me how I do this? I tried Excel but that didn't work for me

re: "I tried Excel but that didn't work for me"Excel worked for me!

Try this:

I'll assume that you have column headings in Row 1 [Skips; 2nds; 3rds] so that your list of names starts in Row 2.

I'll further assume that your lists are in Columns A:C.

1 - In D2 enter this formula:

=RAND()

2 - Drag this down to the bottom of your list, then drag it over to Column F.

3 - This should fill D2:F[the bottom of your list] with Random numbers.

4 - In G2 enter this formula:

=INDEX($A:$C,MATCH(LARGE(D:D,ROW()-1),D:D,0),COLUMN()-6)

5 - Drag this down to the bottom of your list, then drag it over to Column I

This should produce Random teams in Columns G:I

Now, I should warn you that it is possible that the RAND function will produce more than one of the same number in a given column, However, the odds are very, very slim since RAND produces a number between 0 and 1 that has up to 16 decimals places. Should this be an issue, let us know and we'll see what else we can offer.

You should also be aware that the RAND function is volatile meaning that it will recalculate every time the sheet recalculates and will therefore create new random teams. You can eliminate this issue by selecting either all of the Random numbers or all of the Random teams and doing a Copy...Paste Special...Values to lock the values in the cells.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Many many thanks for your reply and for your help .. very much appreciated. I'll give your 'directions' a shot and see how I go. Kindest regards .. and best wishes for the Festive Season

Many many thanks for your help. I truly appreciate it. Team 'selection' is now so much easier !!

Glad I could help. BTW what kind of teams are these?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Bowls. I wanted to do random teams for teams of three. I also want to do teams of 4 so I guess my formula would be ... ($A:$D) ... ?

Cheers

Untested: I shifted everything to account for the extra column of players and the extra column of random numbers.

=INDEX($A:$D,MATCH(LARGE(E:E,ROW()-1),E:E,0),COLUMN()-8)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

You are so kind to do that for me .. thanks ever so much! Kind regards

I thought Lawn Bowls always had teams of 4. You made no mention of Lead's. Which came first...Lawn Bowls or Bocce...or are they not even related?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

No .. lawn bowls can be played by teams of 4 [called Fours]; by 3 [called Triples] by 2 [called Pairs] and by 1 [called Singles .. though you have to have two people playing .. one against the other!!] Bowls and Bocce are very much the same - though in Bocce one seems to 'lob' the ball whereas in bowls one 'delivers' the bowl [to roll smoothly -on a bias] to the jack/kitty]

Bowls looks easy on TV but it's not as easy as it looks - though I guess that goes with all sports??

Cheers

Actually, in Bocce it's up to the player as to whether they want to lob, roll, whatever. The balls have no bias (i.e. they're evenly weighted) so the "delivery technique" is not influenced by the design of the ball as it appears to be in Bowls While it is usually played on a court, in more informal settings (picnics, etc) it's payed on any type of surface available, typically grass.

We find it fun to go all the way around a house as the rounds progress just to keep the terrain changing.

I've Curled on occasion also. When I think of the strategy of curling the stone, it's seems to be akin to the bias of the lawn bowl in that neither is thrown in a straight line.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll