Trying to pair names randomly into teams??

[poop91407]

First off, I'm a novice at excel.

I need some help. I work at a golf course.
At work, I need to randomly pair players together to form teams.

For example:
Saturday morning, there are 19 golfers signed up to play together, but want to paired randomly.

This is what we currently do:
In cells A1:A27 I type their names. In B1:B27, I type their handicaps.

We then use a deck of cards to randomly draw teams. We do this by pulling 4-A's, 4-k's, 4-Q's, 4-J's, and 3-10's from the deck. We shuffle these cards and then go down the names in colum A and assign each palyer a card and place the card value into column C. We then highlight all three columns and sort by column C to form teams.

This works ok, but the problem is they all tee off at the same time and need a "super quick" process to form teams in seconds.

The only variable that I might see being a problem is the # of players vary each time they play. There might be 12 one day and 51 the next. We have to form teams into 4 somes and 3 somes, based on the total number of players we get.

Any help appreciated... The easier the better!! Thanks!

 

[MickG]

NB:-
Line below in red should have 1 added as shown.

c = c + 1
            Cells(c + 1, "C") [COLOR="#FF0000"][B]= Cells(nn + 1, "A")
[/B][/COLOR]            .Remove Cells(nn + 1, "A").Value

 

[KevCarter]

MickG,

Would you please help me with a very rookie question? I'm not really sure how the following line of code works. You are adding the background fill here:

Dn.Resize(, 3).Interior.ColorIndex = oColor


As you know, this line highlights the color of columns A-B-C for each line in a loop.

How do I modify this line to highlight only columns C-D-E in the selected row leaving A&B untouched?

My apologies, I am trying to study the RESIZE function online, but it won't get through my thick skull.

BTW, I've wanted to learn how to run a blind draw selection procedure for a long time. This works beautifully!

Thank you in advance,
Kevin

 

[Matt5353]

Hi Mick thanks for getting back so soon. This is the code couldnt get your others to work.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As Range, RanRng As Range, z As Range, oRes, Ray
Dim i As Integer, j As Integer, real, oSt As Integer, cl As Range
Dim oCol As Integer


Set RanRng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Ray = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Value
ReDim oRes(1 To RanRng.Rows.Count)
RanRng.Offset(, 2).ClearContents
RanRng.Offset(, 20).ClearContents
oCol = 35
For Each cl In RanRng.Offset(, 2)
cl.Interior.ColorIndex = oCol
If cl.Row Mod 2 = 0 And oCol = 34 Then
oCol = 35
ElseIf cl.Row Mod 2 = 0 And oCol = 35 Then
oCol = 34
End If
Next cl


For Each x In RanRng.Offset(, 20)
j = Int(Rnd() * RanRng.Rows.Count) + 1
Set z = RanRng.Offset(, 20).Find(j, lookat:=xlWhole)
While Not z Is Nothing
j = Int(Rnd() * RanRng.Rows.Count) + 1
Set z = RanRng.Offset(, 20).Find(j, lookat:=xlWhole)
Wend
x = j
oRes(x.Row) = j
Next x
ReDim real(1 To RanRng.Rows.Count)


For oSt = 1 To UBound(oRes)
real(oSt) = Cells(oRes(oSt), 1)
Next oSt
Range("C1").Resize(RanRng.Count).Value = Application.Transpose(real)
End Sub

 

[MickG]

KevCarter:-
To highlight columns "C,D & E", try this:-

Dn.offset(, 2).Resize(, 3).Interior.ColorIndex = oCol

 

[MickG]

Matt:-
I think you would be better of with the latest code:-
Have a look at this example.
NB:- If you add new names to column "A", column "C" will remove the Pairings and you will have to re-run the code !!
!https://app.box.com/s/9ntd4q3u59zhwdkfu6t20l5z4fznqcvi

 

[KevCarter]

PERFECT! Thank you Mick.

 

[MickG]

You're welcome

 

[Matt5353]

PERRRRRRRRRRRRRRRRRRfect
Matt

 

[MickG]

You're welcome

 

[Matt5353]

My next question would be. Is it possible to split this into two groups so that you would have the top 30 players paired with the bottom 30 players so you would have more of an even contest rather than the best two players ending up playing the worst two players.
Andrew

Names	Seeding	Names	Seeding	Teams	Pairs
Andrew	1	Bill	31	Team 1	Andrew	1
Fred	2	Mary	32	Joan	33
John	3	Joan	33	Teams 2	Steve	4
Steve	4	Frank	34	Bill	31
				Team 3	John	3
				Mary	32
				Teams 4	Fred	2
				Frank	34

<colgroup><col span="6"><col></colgroup><tbody>
</tbody>