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.

--

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" wrote:

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.

--

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


"Joel" wrote in message
...
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" wrote:

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.

--

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Terry" wrote in message
...
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


"Joel" wrote in message
...
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" wrote:

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(OFFS
ET(Random,0,0,COUNTA(Players),1),ROW()-ROW(TopLeft)+1),OFFSET(Random,0,0,COU
NTA(Players),1),0)),"")

and this is the formula in Col D:

=IF(ROW()-ROW(TopLeft)<INT(COUNTA(Players)/2),INDEX(Players,MATCH(SMALL(OFFS
ET(Random,0,0,COUNTA(Players),1),ROW()-ROW(TopLeft)+1),OFFSET(Random,0,0,COU
NTA(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.

--

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



"Ragdyer" wrote in message
...
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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"Terry" wrote in message
...
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


"Joel" wrote in message
...
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" wrote:

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(OFFS
ET(Random,0,0,COUNTA(Players),1),ROW()-ROW(TopLeft)+1),OFFSET(Random,0,0,COU
NTA(Players),1),0)),"")

and this is the formula in Col D:

=IF(ROW()-ROW(TopLeft)<INT(COUNTA(Players)/2),INDEX(Players,MATCH(SMALL(OFFS
ET(Random,0,0,COUNTA(Players),1),ROW()-ROW(TopLeft)+1),OFFSET(Random,0,0,COU
NTA(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.

--

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 !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
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.
--
HTH,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"Terry" wrote in message
...
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


"Joel" wrote in message
...
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" wrote:

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(OFFS

ET(Random,0,0,COUNTA(Players),1),ROW()-ROW(TopLeft)+1),OFFSET(Random,0,0,COU
NTA(Players),1),0)),"")

and this is the formula in Col D:


=IF(ROW()-ROW(TopLeft)<INT(COUNTA(Players)/2),INDEX(Players,MATCH(SMALL(OFFS

ET(Random,0,0,COUNTA(Players),1),ROW()-ROW(TopLeft)+1),OFFSET(Random,0,0,COU
NTA(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.

--

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)

--
Biff
Microsoft Excel MVP


"Terry" wrote in message
...
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.

--

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)))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"Terry" wrote in message
...
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.

--

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


"Terry" wrote in message
...
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.

--

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!


--
Biff
Microsoft Excel MVP


"MartinW" wrote in message
...
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


"Terry" wrote in message
...
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.

--

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" wrote in message
...
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)

--
Biff
Microsoft Excel MVP

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Terry" wrote in message
...
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" wrote in message
...
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)

--
Biff
Microsoft Excel MVP