Unique Random List

[Dave]

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:I24))
Column I & K use the =RAND() formula

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

Thanks for helping!
Dave

 

[joeu2004]

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

 

[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?

 

[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.

 

[Max]

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 C33 down to D23. This produces a random scramble of the source items
in D323.

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

 

[Dave]

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

 

[joeu2004]

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!

 

[Max]

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

 

[Bernd P]

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 here:
http://www.sulprobil.com/html/uniqrandint.html

Regards,
Bernd

 

[Ragdyer]

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

 

[Dave]

Thanks Max. That works!

I have a couple of other formulas in my score card I may need help with.
I'll let you know.

Again, thank you.
Dave

 

[Max]

Welcome, Dave. Pl start new threads for new queries. I would also kindly
suggest that you keep it general in the newsgroups, ie do not direct your
question(s) at any particular responder. Keep it open. Avail your query to
all interested responders out there who might have something to offer you.
Cheers.

p/s: I'm not into golf ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500, Files:358, Subscribers:55
xdemechanik

 

[Dave]

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

 

[Bison405]

Here's a simple but elegant way to do this:
Put the list of names in column A. I'm using 10 names but the list could be
as long as you like if you adjust accordingly.

In cells A1 through A10, enter numbers, names, or any list you want to
randomize
In cell B1, enter =RAND() and copy down to B10
In cell C1, enter =OFFSET($A$1,RANK(B1,$B$1:$B$10)-1,0) and copy to cell
C10

This will copy all the "Names" in column A in column C but in a random order
that changes every time you press the F9 key. If you want them as random
pairings, put:
=C1 in cell D1 and copy down to D5, then put
=C6 in cell E1 and copy down to E5.
Pair Column D with Column E

Clearly you need an even number of "players" in column A with half going to
Column D and half to column E.