Formula to Randomly Pair Two Golfers

[Terry]

This is directed primarily to Harlan Grove because he provided the
original formula a bit more than 5 years ago (and I see he's still
here), but I guess anybody can feel free to jump right in. Harlan's
original post is appended below at the end of my new request.

This a little long and for that I apologize. I just want to be sure
that I'm as clear as I can be to anyone who reads this.

The formula I need help with is one I've used for more than 5 years. It
randomly pairs two golfers **after** the scores are turned in and
posted. We call that a blind draw, but in this case it's done after
play rather than before.

Until now, the worksheet in question has consisted of 4 columns of 36
rows. I need to expand it to 4 columns of 48 rows and therein lies my
problem. For reasons I do not understand, the formulas in Cols C and D
simply will not copy into additional rows. They seem to be
self-limiting to 36 rows.

Here's the criteria: Col A (A1:A48) is merely a line number, 1 through
48.

Col B (B1:B48) contains this formula " =Rand()"

Col C contains this formula:
=IF(ROW()-ROW(TopLeft)<INT(COUNTA(Players)/2),INDEX(Players,MATCH(LARGE(OFFSET(Random,0,0,COUNTA(Players),1),ROW()-ROW(TopLeft)+1),OFFSET(Random,0,0,COUNTA(Players),1),0)),"")

and this is the formula in Col D:
=IF(ROW()-ROW(TopLeft)<INT(COUNTA(Players)/2),INDEX(Players,MATCH(SMALL(OFFSET(Random,0,0,COUNTA(Players),1),ROW()-ROW(TopLeft)+1),OFFSET(Random,0,0,COUNTA(Players),1),0)),"")

The result has been simply two columns of paired numbers from 1 through
36. I need those numbers to go up to 48.

It has worked very well over the years and takes me about 30 minutes to
do the data entry.

If necessary, I'll be glad to provide additional info. Thanks very much
in advance.

Terry

Here's the original posting from Harlan Grove, which I have saved lo,
these many years.

----- Original Message -----
From: "Harlan Grove" <[email protected]>
Newsgroups: microsoft.public.excel.worksheet.functions
Sent: Monday, May 05, 2003 1058
Subject: Re: Formula to Randomly Select Pairs

 

[Joel]

The problem is you have named cells and they have to be changes. Go to
Insert Menu - Names - Define and make appropriate changes. it is set for 36
players and the range of theh input data is set to only 36 cells.

 

[Terry]

The immediate problem here is that I don't know what the "appropriate
changes" are. When I did this 5 years ago, I merely inserted the
formula that was given to me in a post on this NG. I did not alter one
jot or tittle and it worked fine. I said "thank you" and never looked
back. Now, I need more help. I do not know how to make the changes
you're telling me I need. I've looked at on old, very elementary "MS
Office for Windows 95" but it's no help. I'm using Excel 2002 (SP3).

If you could perhaps be a bit more explicit, I'd certainly appreciate
it. Most of the terms used in the formula itself mean almost nothing to
me. I can write a fairly simple IF-THEN or IF-THEN-ELSE formula, but
this is well over my head.

Terry

 

[Ragdyer]

If you'd be interested in a new formula, I could make a suggestion using a
procedure that I'm familiar with ... not taking anything away from Harlan's
formulas.

In an out-of-the-way location of your sheet, say Z1 to Z100 (sizing for
future expansion, so you don't have to go through this again in another 5
years), enter the RAND FUNCTION:
=RAND()

Now, in Column Y enter the names of the players.
Of course, just fill in the number of players you currently have, assuming
that there's an *even* number!

Let's assume that you want the paired names to display in Column A and
Column B.
Enter this formula in A1:

=INDEX($Y$1:INDEX($Y:$Y,COUNTA($Y$1:$Y$100)),RANK(INDEX($Z$1:INDEX($Z:$Z,COU
NTA($Y$1:$Y$100)),(2*ROWS($1:1))-2+COLUMNS($A:A)),$Z$1:INDEX($Z:$Z,COUNTA($Y
$1:$Y$100))))

Copy across to B1, then select both A1 and B1 and copy down as needed.

All you have to do is add any new names to Column Y, keeping the rest of
Column Y empty.

If you should ever exceed 100 names, just expand the ranges in the formula
to include those additional names.
In fact, you could even do it now by revising them all to 200, if you wish.

 

[Terry]

Thank you for the rapid response. I'll have to spend some time on it
tomorrow or Monday. Your post arrived just as I was about to shut down
for the night. Again, thank you.

Terry

If you'd be interested in a new formula, I could make a suggestion
using a
procedure that I'm familiar with ... not taking anything away from
Harlan's
formulas.

In an out-of-the-way location of your sheet, say Z1 to Z100 (sizing
for
future expansion, so you don't have to go through this again in
another 5
years), enter the RAND FUNCTION:
=RAND()

Now, in Column Y enter the names of the players.
Of course, just fill in the number of players you currently have,
assuming
that there's an *even* number!

Let's assume that you want the paired names to display in Column A and
Column B.
Enter this formula in A1:

=INDEX($Y$1:INDEX($Y:$Y,COUNTA($Y$1:$Y$100)),RANK(INDEX($Z$1:INDEX($Z:$Z,COU
NTA($Y$1:$Y$100)),(2*ROWS($1:1))-2+COLUMNS($A:A)),$Z$1:INDEX($Z:$Z,COUNTA($Y
$1:$Y$100))))

Copy across to B1, then select both A1 and B1 and copy down as needed.

All you have to do is add any new names to Column Y, keeping the rest
of
Column Y empty.

If you should ever exceed 100 names, just expand the ranges in the
formula
to include those additional names.
In fact, you could even do it now by revising them all to 200, if you
wish.

 

[Ragdyer]

Don't forget, each hit of <F9> will give you a new random selection.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

If you'd be interested in a new formula, I could make a suggestion using a
procedure that I'm familiar with ... not taking anything away from Harlan's
formulas.

In an out-of-the-way location of your sheet, say Z1 to Z100 (sizing for
future expansion, so you don't have to go through this again in another 5
years), enter the RAND FUNCTION:
=RAND()

Now, in Column Y enter the names of the players.
Of course, just fill in the number of players you currently have, assuming
that there's an *even* number!

Let's assume that you want the paired names to display in Column A and
Column B.
Enter this formula in A1:

=INDEX($Y$1:INDEX($Y:$Y,COUNTA($Y$1:$Y$100)),RANK(INDEX($Z$1:INDEX($Z:$Z,COUNTA($Y$1:$Y$100)),(2*ROWS($1:1))-2+COLUMNS($A:A)),$Z$1:INDEX($Z:$Z,COUNTA($Y
$1:$Y$100))))

Copy across to B1, then select both A1 and B1 and copy down as needed.

All you have to do is add any new names to Column Y, keeping the rest of
Column Y empty.

If you should ever exceed 100 names, just expand the ranges in the formula
to include those additional names.
In fact, you could even do it now by revising them all to 200, if you wish.

--
HTH,

RD

--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit !
-------------------------------------------------------------------------- -

The immediate problem here is that I don't know what the "appropriate
changes" are. When I did this 5 years ago, I merely inserted the
formula that was given to me in a post on this NG. I did not alter one
jot or tittle and it worked fine. I said "thank you" and never looked
back. Now, I need more help. I do not know how to make the changes
you're telling me I need. I've looked at on old, very elementary "MS
Office for Windows 95" but it's no help. I'm using Excel 2002 (SP3).

If you could perhaps be a bit more explicit, I'd certainly appreciate
it. Most of the terms used in the formula itself mean almost nothing to
me. I can write a fairly simple IF-THEN or IF-THEN-ELSE formula, but
this is well over my head.

Terry

=IF(ROW()-ROW(TopLeft)<INT(COUNTA(Players)/2),INDEX(Players,MATCH(LARGE(OFFSET(Random,0,0,COUNTA(Players),1),ROW()-ROW(TopLeft)+1),OFFSET(Random,0,0,COU

NTA(Players),1),0)),"")

=IF(ROW()-ROW(TopLeft)<INT(COUNTA(Players)/2),INDEX(Players,MATCH(SMALL(OFFSET(Random,0,0,COUNTA(Players),1),ROW()-ROW(TopLeft)+1),OFFSET(Random,0,0,COU

 

[T. Valko]

This seems to do the same thing as Harlan's formulas.

A1:An = player names or numbers

I'm assuming you enter this data as a contiguous block.

Enter this formula in B1 and copy down to B48:

=IF(A1="","",RAND())

Enter this formula in D1:

=IF(ROWS($1:1)<=COUNTA(A:A)/2,INDEX(A:A,MATCH(SMALL(B:B,ROWS($1:1)),B:B,0)),"")

Enter this formula in E1:

=IF(ROWS($1:1)<=COUNTA(A:A)/2,INDEX(A:A,MATCH(LARGE(B:B,ROWS($1:1)),B:B,0)),"")

Select both D1 and E1 and copy down to row 24 (max of 48 players at 2 per
team = 24)

 

[T. Valko]

Slight tweak:

Enter this formula in E1:
=IF(ROWS($1:1)<=COUNTA(A:A)/2,INDEX(A:A,MATCH(LARGE(B:B,ROWS($1:1)),B:B,0)),"")

We only need to count the rows once so change the formula in E1 to:

=IF(D1="","",INDEX(A:A,MATCH(LARGE(B:B,ROWS($1:1)),B:B,0)))

 

[MartinW]

Hi All,

I must be missing something here but all of these formulae
seem to be overcomplicating the matter.

With numbers 1 to 48 in A1:A48
And =RAND() in B1:B48

Put this in D1
=INDEX(A:A,ROWS($1:1)*2-1)

Put this in E1
=INDEX(A:A,ROWS($1:1)*2)


Drag D1 and E1 down to row 24
Then with B1 as the active cell succesive clicks on the
Sort Ascending button will give randomly selected
pairings in columns D and E.

It would probably be simpler to use the players names
in column A rather than numbers, the formulae
work on both text and numbers.

HTH
Martin

 

[T. Valko]

If I put the numbers 1 to 12 (representing the players) in A1:A12 then I see
B13:B48 full of rand numbers that I don't want to see. Just an aesthetic
thing but I would "hide" all unused rows. Same thing with columns D and E.
All those unused rows show 0s. Not aesthetically pleasing. As per the OP.

The list of names may vary from as low as 12 to as high as 36 [48] on any
given day.

Sorting takes 2 steps while hitting function key F9 takes 1 step.

So, if you were to write the formulas to take care of all the unused rows
the difference between our methods can be expressed as:: 6 of one and a half
dozen of the other!

 

[Terry]

Thank you very much! You're right, this produces the same result as the
formula I've been using and the result is what I'm accustomed to seeing.
I decided to ignore the tweak you offered in your next post. Why?
Because the formulas in this one are identical except for one word. It
just seemed the simplest thing to do. Even better, I almost understand
how/why this one works.

My thanks also to the others who offered their assistance.

Terry

 

[T. Valko]

You're welcome. Thanks for the feedback!