Random name generator - how to make it not repeat?

[yplin]

I am currently making random name generators for my classes. The student numbers vary from 18 to 53 in each class. The formula introduced in one of the youtube videos helped to generate names randomly, but I still found several repetitions.

For example, I've got a class with 33 three students. Here's the formula I created, according to the youtube:

Column A1-A33: names
Column B1:=(INDEX(A1:A33,INT(RAND()*33+1)))

I was trying to find out how randomly the names would be generated. The problem is when I hit the F9 for 33 times, some of the names appeared 3 times, while others appeared not even once.

My OS is windows XP with excel 2003. How can I make each name appear only once? Thanks for help.

 

[Tom Urtis]

"some of the names appeared 3 times, while others appeared not even once"

Ergo randomness, as you know.

The question you are asking sounds reasonable on the face of it, but if you have one single formula and as I understand it, each time you recalculate the formula, you want to return one of 33 names, with the qualifier that the next name selected cannot have been returned yet. On the 33rd recalc, the return name won't be random, it will be the only name from the decreasing pool, and therefore predictable.

You can rearrrange the list in a way that redistributes itself randomly without any duplicates, but then the issue is, which name in that list do you select randomly, still faced with the probability of eventually selecting the same name at random.

Why are you doing this; what is the reason in the first place? VBA might come in handy to make the solution more efficiently achievable than a formula can.

 

[Marcelo Branco]

Hi,

Assuming you have your names in Sheet1 A2:A? (no duplicates)

The macro below generates the names in a random order in Sheet1 B2:B?
(try it on a test-workbook)

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=64>Names</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>Random</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Name1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Name8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Name2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Name3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Name3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Name10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Name4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Name4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Name5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Name9</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Name6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Name6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Name7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Name2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Name8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Name7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Name9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Name1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Name10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Name5</TD></TR></TBODY></TABLE>

Sub randomCollection()
    Dim Names As New Collection
    Dim lastRow As Long, i As Long, j As Long, lin As Long
    Dim wk As Worksheet
 
    Set wk = Sheets("Sheet1")
 
    With wk
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
 
    For i = 2 To lastRow
        Names.Add wk.Cells(i, 1).Value, CStr(wk.Cells(i, 1).Value)
    Next i
 
    lin = 1
    For i = lastRow - 1 To 1 Step -1
        j = Application.WorksheetFunction.RandBetween(1, i)
        lin = lin + 1
        Range("B" & lin) = Names(j)
        Names.Remove j
    Next i
 
End Sub

HTH

M.

 

[yplin]

Thanks Tom and Marcelo for replying. I'm an English teacher from Taiwan, and I'm doing this because I've got more than 400 students in 13 classes, and they are supposed to take turns for the speaking test. I was hoping that I could create a random name generator to increase some excitement, but probably I'm thinking too much.

I know there are a few programmes which can also do the job. 'The Hat' can do it, but the thing is it doesn't really support Chinese characters. Others might be written in Java script or PHP, which I don't think the old PC system at the school are capable for those.

For Marcelo, thanks for giving me the instructions. Unfortunately, I am quite illiterate to excel, so I cannot understand it.

Thanks again for your help.

 

[energman58]

If your names are in column A starting in A2

The following formula will give you a random name every time you recalculate it (it also assumes your students are never called "zzzzzzzzzz"). It works with any number of names as long as there are no gaps - "zzzzzzzzzzzz" is shorthand for teh last place in an alphabetical listing - you might need to change this if you are using Chinese characters to whatever teh equivalent is.

=INDEX(A2:INDIRECT("A"&(MATCH("zzzzzzzzz",A:A,1))),ROUND(RAND()*COUNTA(A2:INDIRECT("A"&(MATCH("zzzzzzzzz",A:A,1)))),0))

It will not however know anything about what was selected before so will quite often return the same name twice (whic is what random means). To get around this you need quite a lot more logic or intermediate results so you draw without replacing.

If you pm me with an email I have a ss which selects readers for church lessons subject to a number of constraints which might give you some ideas

 

[AMAS]

Excellent code Marcelo. Thanks for sharing.

AMAS

 

[jasonb75]

Will this method using iterative calculation work for you?

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 75px"><COL style="WIDTH: 75px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Name 1</TD><TD style="TEXT-ALIGN: right">0.37088195</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Name 2</TD><TD style="TEXT-ALIGN: right">0.46846421</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Name 3</TD><TD style="TEXT-ALIGN: right">0.29665758</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Name 4</TD><TD style="TEXT-ALIGN: right">0.19446468</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Name 5</TD><TD style="TEXT-ALIGN: right">0.02384601</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD style="TEXT-ALIGN: right">27/03/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD>Name 2</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=IF(OR($B$8=0,$B$8>COUNT($B$1:$B$5)),RAND(),B1)</TD></TR><TR><TD>B2</TD><TD>=IF(OR($B$8=0,$B$8>COUNT($B$1:$B$5)),RAND(),B2)</TD></TR><TR><TD>B3</TD><TD>=IF(OR($B$8=0,$B$8>COUNT($B$1:$B$5)),RAND(),B3)</TD></TR><TR><TD>B4</TD><TD>=IF(OR($B$8=0,$B$8>COUNT($B$1:$B$5)),RAND(),B4)</TD></TR><TR><TD>B5</TD><TD>=IF(OR($B$8=0,$B$8>COUNT($B$1:$B$5)),RAND(),B5)</TD></TR><TR><TD>B7</TD><TD>=IF(OR(B8=0,B8>COUNT($B$1:$B$5)),TODAY(),B7)</TD></TR><TR><TD>B8</TD><TD>=NETWORKDAYS(B7,TODAY())+1</TD></TR><TR><TD>B9</TD><TD>=INDEX($A$1:$A$5,RANK(INDEX($B$1:$B$5,$B$8),$B$1:$B$5))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

The selection will randomize then the table is created and the random values will lock in place, the formula in B7 will generate the current date and lock it in place, this returns a value of 1 to B8, which will increase daily (only on weekdays) until it exceeds the count of names in the list.

At this point the the table will randomize again and the formula in B7 will change to the new current date and reset B8 to 1, restarting the cycle.

Note for this to work correctly, you need to enable iterative calculation in excel options before entering any of the formula.

Also the formula in B1:B5 and B7 need to be entered before the formula in B8.

Hope that makes sense, I can see it better than explain it.

 

[Marcelo Branco]

Excellent code Marcelo. Thanks for sharing.

AMAS

AMAS,

Tks very much. I apreciated it
i`m glad that you liked the code.

All the best

M.

 

[melson titular]

bravo marcelo!
Mabuhay!.., you saved me.