I have a list in 'sheet1' that contains names. B3:B23
In 'sheet2' I have 2 columns that I want to use to pair the list in random
order.
I used this formula in column A
=INDEX('Sign-Up Sheet'!B3:B23,RANK(OFFSET($K$1,2*ROW(A1)-1,0),K2:K24))
And this formula in column C
=INDEX('Sign-Up Sheet'!B3:B23,RANK(OFFSET($I$1,2*ROW(E1)+1,0),I2:I 24))
Column I & K use the =RAND() formula

The problem is that it has duplicate names instead of matching unique pairs.

Thanks for helping!
Dave

On Aug 23, 8:44 am, Dave wrote:
I have a list in 'sheet1' that contains names. B3:B23
In 'sheet2' I have 2 columns that I want to use to pair the list
in random order.

Does this do what you want?

Put =RAND() into K2:K22. I usually then copy-and-paste-special-value
so the random numbers do not keep changing.

Then put the following formula into the first cell of column A and
copy down 9 rows:

=index($B$3:$B$23, rank(offset($K$1,2*row(A1)-1,0), $K$2:$K$22))

and put the following formula into the first cell of column C and copy
down 9 rows:

=index($B$3:$B$23, rank(offset($K$1,2*row(A1),0), $K$2:$K$22))

Some notes:

1. The number of random numbers, K2:K22, should be the same as the
number of names in column -- 21 items in B3:B23.

2. You can have only 10 pairs in 21 names; one name is not used. Is
that really your intent?


----- original posting -----

On Aug 23, 8:44*am, Dave wrote:
I have a list in 'sheet1' that contains names. B3:B23
In 'sheet2' I have 2 columns that I want to use to pair the list in random
order.
I used this formula in column A
=INDEX('Sign-Up Sheet'!B3:B23,RANK(OFFSET($K$1,2*ROW(A1)-1,0),K2:K24))
And this formula in column C
=INDEX('Sign-Up Sheet'!B3:B23,RANK(OFFSET($I$1,2*ROW(E1)+1,0),I2:I 24))
Column I & K use the =RAND() formula

The problem is that it has duplicate names instead of matching unique pairs.

Thanks for helping!
Dave

Thanks for your help. This is a list that may be added to. But I couldn't
figure a way to not include blank entries. Example, I have names in rows
B3:B23, but I would like to have rows B3:B50 so that I don't have to keep
changing the formula as I add new names.

I'll try your formula later to see how it works. My only question would be
if I could change the pairings multiple times?

"joeu2004" wrote:

On Aug 23, 8:44 am, Dave wrote:
I have a list in 'sheet1' that contains names. B3:B23
In 'sheet2' I have 2 columns that I want to use to pair the list
in random order.

Does this do what you want?

Put =RAND() into K2:K22. I usually then copy-and-paste-special-value
so the random numbers do not keep changing.

Then put the following formula into the first cell of column A and
copy down 9 rows:

=index($B$3:$B$23, rank(offset($K$1,2*row(A1)-1,0), $K$2:$K$22))

and put the following formula into the first cell of column C and copy
down 9 rows:

=index($B$3:$B$23, rank(offset($K$1,2*row(A1),0), $K$2:$K$22))

Some notes:

1. The number of random numbers, K2:K22, should be the same as the
number of names in column -- 21 items in B3:B23.

2. You can have only 10 pairs in 21 names; one name is not used. Is
that really your intent?


----- original posting -----

On Aug 23, 8:44 am, Dave wrote:
I have a list in 'sheet1' that contains names. B3:B23
In 'sheet2' I have 2 columns that I want to use to pair the list in random
order.
I used this formula in column A
=INDEX('Sign-Up Sheet'!B3:B23,RANK(OFFSET($K$1,2*ROW(A1)-1,0),K2:K24))
And this formula in column C
=INDEX('Sign-Up Sheet'!B3:B23,RANK(OFFSET($I$1,2*ROW(E1)+1,0),I2:I 24))
Column I & K use the =RAND() formula

The problem is that it has duplicate names instead of matching unique pairs.

Thanks for helping!
Dave

This addresses your 2 requests:
Allow for expansion of the sign up list without having to revise the
formula,
And make multiple changes in the pairings easily available.

Let's first change the calc mode of the WB to "Manual".
From the Menu Bar:
<Tools <Options <Calculation tab,
And click on "Manual", then <OK.

This will *prevent* your pairings list from accidentally changing *until*
you hit <F9.

Following your example, say that the sizing of the list could go from B3 to
B100.
So, on your Sheet2, fill Column K with the Rand() function from K2 to K100.

Now, enter this formula in Column A of Sheet2 (actually, you can enter it
*anywhere* you wish):

=INDEX('Sign-Up Sheet'!$B$3:$B$100,RANK(INDEX($K:$K,2*ROWS($1:2)-2),
$K$2:INDIRECT("K"&COUNTA('Sign-Up Sheet'!$B$3:$B$100)+1)))

And enter this formula in Column C of Sheet2 (again, OR anywhere you wish -
should be adjacent to first formula):

=INDEX('Sign-Up Sheet'!$B$3:$B$100,RANK(INDEX($K:$K,2*ROWS($1:2)-1),
$K$2:INDIRECT("K"&COUNTA('Sign-Up Sheet'!$B$3:$B$100)+1)))

These 2 formulas will give you your first set of pairings.

Copy both down until you run out of names.

To change the pairings, and get a new list, simply hit <F9.

Don't forget, since you placed the calc mode in "Manual" at the start,
You'll need to hit <F9 right after you copy those formulas down.

Now, if you copied down and displayed the #N/A error, meaning you ran out of
names, those errors will change to names as you add more names to the list
and hit <F9.

--
HTH,

RD

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

"Dave" wrote in message
...
Thanks for your help. This is a list that may be added to. But I couldn't
figure a way to not include blank entries. Example, I have names in rows
B3:B23, but I would like to have rows B3:B50 so that I don't have to keep
changing the formula as I add new names.

I'll try your formula later to see how it works. My only question would be
if I could change the pairings multiple times?

"joeu2004" wrote:

On Aug 23, 8:44 am, Dave wrote:
I have a list in 'sheet1' that contains names. B3:B23
In 'sheet2' I have 2 columns that I want to use to pair the list
in random order.

Does this do what you want?

Put =RAND() into K2:K22. I usually then copy-and-paste-special-value
so the random numbers do not keep changing.

Then put the following formula into the first cell of column A and
copy down 9 rows:

=index($B$3:$B$23, rank(offset($K$1,2*row(A1)-1,0), $K$2:$K$22))

and put the following formula into the first cell of column C and copy
down 9 rows:

=index($B$3:$B$23, rank(offset($K$1,2*row(A1),0), $K$2:$K$22))

Some notes:

1. The number of random numbers, K2:K22, should be the same as the
number of names in column -- 21 items in B3:B23.

2. You can have only 10 pairs in 21 names; one name is not used. Is
that really your intent?


----- original posting -----

On Aug 23, 8:44 am, Dave wrote:
I have a list in 'sheet1' that contains names. B3:B23
In 'sheet2' I have 2 columns that I want to use to pair the list in
random
order.
I used this formula in column A
=INDEX('Sign-Up Sheet'!B3:B23,RANK(OFFSET($K$1,2*ROW(A1)-1,0),K2:K24))
And this formula in column C
=INDEX('Sign-Up Sheet'!B3:B23,RANK(OFFSET($I$1,2*ROW(E1)+1,0),I2:I 24))
Column I & K use the =RAND() formula

The problem is that it has duplicate names instead of matching unique
pairs.

Thanks for helping!
Dave

I tried it and it still gives me duplicates. I'm not sure if I did the
=Rand() right though. I dragged the right corner down. I'm not sure what you
mean by the "paste special".

Also, do I need to "CSE" the formula? That's what I did and dragged down.

"joeu2004" wrote:

On Aug 23, 8:44 am, Dave wrote:
I have a list in 'sheet1' that contains names. B3:B23
In 'sheet2' I have 2 columns that I want to use to pair the list
in random order.

Does this do what you want?

Put =RAND() into K2:K22. I usually then copy-and-paste-special-value
so the random numbers do not keep changing.

Then put the following formula into the first cell of column A and
copy down 9 rows:

=index($B$3:$B$23, rank(offset($K$1,2*row(A1)-1,0), $K$2:$K$22))

and put the following formula into the first cell of column C and copy
down 9 rows:

=index($B$3:$B$23, rank(offset($K$1,2*row(A1),0), $K$2:$K$22))

Some notes:

1. The number of random numbers, K2:K22, should be the same as the
number of names in column -- 21 items in B3:B23.

2. You can have only 10 pairs in 21 names; one name is not used. Is
that really your intent?


----- original posting -----

On Aug 23, 8:44 am, Dave wrote:
I have a list in 'sheet1' that contains names. B3:B23
In 'sheet2' I have 2 columns that I want to use to pair the list in random
order.
I used this formula in column A
=INDEX('Sign-Up Sheet'!B3:B23,RANK(OFFSET($K$1,2*ROW(A1)-1,0),K2:K24))
And this formula in column C
=INDEX('Sign-Up Sheet'!B3:B23,RANK(OFFSET($I$1,2*ROW(E1)+1,0),I2:I 24))
Column I & K use the =RAND() formula

The problem is that it has duplicate names instead of matching unique pairs.

Thanks for helping!
Dave

Another play to tinker with ..

Source data in B3:B23 (21 items)

In C3: =RAND()
In D3: =INDEX($B$3:$B$23,RANK(C3,$C$3:$C$23))
Copy C3:D3 down to D23. This produces a random scramble of the source items
in D3:D23.

Then to re-arrange the 21 scrambled items in a 3C x 7R grid elsewhere,
eg you could place in say, F2:
=INDEX($D$3:$D$23,COLUMNS($A:A)+ROWS($1:1)*3-3)
Copy F2 across/fill down to H9 to populate a 3C x 7R grid
Press F9 to regenerate
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---

Hi Max,
I tried that but it gets 0's #ref along with some names. The formula
joeu2004 gave me works, it just allows duplicates and I need all names
paired. This is for a golf tournament where I pair players at random.
Thanks,
Dave


"Max" wrote:

Another play to tinker with ..

Source data in B3:B23 (21 items)

In C3: =RAND()
In D3: =INDEX($B$3:$B$23,RANK(C3,$C$3:$C$23))
Copy C3:D3 down to D23. This produces a random scramble of the source items
in D3:D23.

Then to re-arrange the 21 scrambled items in a 3C x 7R grid elsewhere,
eg you could place in say, F2:
=INDEX($D$3:$D$23,COLUMNS($A:A)+ROWS($1:1)*3-3)
Copy F2 across/fill down to H9 to populate a 3C x 7R grid
Press F9 to regenerate
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---

Try this working sample which illustrates 2 random scrambles:
1. 22 items into a 2C x 11R grid (this should be what you're after)
2. 21 items into a 3C x 7R grid (the earlier response)

http://freefilehosting.net/download/3lh0e
Random scrambling a col list n rearrange into a grid.xls
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"Dave" wrote:
Hi Max,
I tried that but it gets 0's #ref along with some names. The formula
joeu2004 gave me works, it just allows duplicates and I need all names
paired. This is for a golf tournament where I pair players at random.
Thanks,
Dave

On Aug 23, 12:20*pm, Dave wrote:
I tried it and it still gives me duplicates.

I have no idea why it doesn't work for you. Works fine for me. I
wonder if I am misunderstanding your requirements, or if there is some
details about your situation that you neglected to mention.

I suggest you try the following. Assuming you get it to work, perhaps
then you can realize what is different about your situation.

Enter the numbers 1 through 21 in B3:B23. Then follow the
instructions in my previous posting.

You might also do the following, if it helps you understand what is
going on. Enter the following formula in some cell, then copy down 20
rows:

=index($B$3:$B$23, rank(K2, $K$2:$K$22))

With all of these formulas, pay close attention to where I use "$" and
where I don't. Perhaps you can cut-and-paste them as I wrote them,
then edit to customize them for your workbook.


I'm not sure if I did the =Rand() right though. I dragged
the right corner down.

Works for me. If all the cells look the same when you are done, the
problem might be that you disabled auto calculation. Click on Tools /
Options / Calculation and set Automatic Calculation. Or press F9 to
force a manual calculation.


I'm not sure what you mean by the "paste special".

Select all of the cells with the =RAND(). Click Edit / Copy (or
simply type ctrl-C). Then click Edit / Paste Special and select
Values. Caveat: this replaces the =RAND() formulas.


Also, do I need to "CSE" the formula? That's what I did
and dragged down.

My formulas are not array formulas, so you do not need ctrl-shift-
Enter. However, CSE does not seem to have any adverse effect on these
formulas. It's just needless.

Good luck!

Hello Dave,

Sub pair_them()
Dim r As Range, rSource As Range, rDest As Range
Dim i As Long
Dim v

Set rSource = Sheets("Sheet1").Range("B3")
Set rDest = Sheets("Sheet2").Range("B3")

Set r = Range(rSource, rSource.End(xlDown))

Range(rDest, rDest.Offset(0, 1).End(xlDown)).ClearContents

For Each v In VBUniqRandInt(r.Count, r.Count)
rDest.Offset(Int(i / 2), i Mod 2) = r(v)
i = i + 1
Next v

End Sub

Change source and destination cells if you like.

My UDF VBUniqRandInt you will find he
http://www.sulprobil.com/html/uniqrandint.html

Regards,
Bernd

Thanks to all of you for your suggestions. I'm new to using this forum and
Excel formulations. Bernd P, I think that must be a macro? I'm unfamiliar
with that type of code. It might be more difficult for now. I may give a
whirl someday!
Thanks again
Dave

"Dave" wrote:

I have a list in 'sheet1' that contains names. B3:B23
In 'sheet2' I have 2 columns that I want to use to pair the list in random
order.
I used this formula in column A
=INDEX('Sign-Up Sheet'!B3:B23,RANK(OFFSET($K$1,2*ROW(A1)-1,0),K2:K24))
And this formula in column C
=INDEX('Sign-Up Sheet'!B3:B23,RANK(OFFSET($I$1,2*ROW(E1)+1,0),I2:I 24))
Column I & K use the =RAND() formula

The problem is that it has duplicate names instead of matching unique pairs.

Thanks for helping!
Dave