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,COU NTA(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,COU NTA(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"
Newsgroups: microsoft.public.excel.worksheet.functions
Sent: Monday, May 05, 2003 1058
Subject: Formula to Randomly Select Pairs


"Terry" wrote...
I need help with a formula. More honestly, I need a formula --
because I have
no idea how to begin. I would like to have a formula which will
randomly pair
two people as partners. This is for a golf group, in case anyone's
interested. The list of names may vary from as low as 12 to as high
as 36 on
any given day. As each person joins the group, they are assigned a
number. I
could do this by hand by merely drawing numbers out of a hat, but I'd
like to
do it by formula using Excel, if possible. The only criterion I can
think of
is that no number be used more than once.

This could be solved with a simple data structure and no circular
recalculation.
If there could be as many as 36 players but no more than this, name
some 36-row,
single column range Players. I'll use A1:A36 as an example. Enter
player names
in this range - don't fill in unneeded cells (so if you have, say, 20
players,
fill in A1:A20 and leave A21:A36 blank). Enter the formula =RAND() in
each cell
of another 36-row, single column range, and name that range Random.
I'll use
B1:B36 in this example, but this range doesn't need to be adjacent to
the
Players range. The random pairs would be generated in at most an
18-row,
2-column range. Name the top-left cell in that range TopLeft. I'll use
D1:E18 as
an example.

Then enter the following formula in TopLeft (D1)

=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 the following formula in the cell immediately to its right (E1)

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

Now select these two cells and fill them down into the next 17 rows,
so in my
example fill D1:E1 down into D2:E18.

--