Excel random numbers

[jmg522]

Hello, I am trying to randomly assign 300 people to 50 6-person tables. Is there a way to expand the RANDBETWEEN function to assign a number between 1 and 50 to the list of 300 people, with each number between 1 and 50 appearing exactly six times?

Any help is appreciated!

Thanks

 

[Andrew Poulsom]

Welcome to MrExcel.

Why don't you just assign 1 to 300, then have the first 6 on table 1, the second on table 2, etc?

 

[Dannyh1]

If you didn't want to use Andrew more efficient way

You could try this

A1:A300 = 1 to 300
D1:D50 = 1 to 50

E1 =COUNTIF($B$1:$B$300,D1) Copy down to E50

F1 =IF(E1<>6,1,0)

G1 =SUM(F1:F50)

Then run this code:

Sub Change()
        
        
        
Do Until Range("G1").Value = 0
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(1,50)"
Selection.Copy
Range("B1:B300").Select
ActiveSheet.Paste
Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
Loop
 
End Sub

Beware though - I tried this & stopped it after 10mins - could take a while (that's if it ever gets there)!

 

[jmg522]

Hi Andrew - thanks for that info, seems a more simple way to do it. A few follow up questions.. when assigning each person a number between 1 and 300 is there a way within the RANDBETWEEN function to specify that I want each number to only appear once? Second, is there a way to highlight all my participants and have the numbers generated all at once for them instead of going person by person and generating the number?

Thanks again

 

[Andrew Poulsom]

This may help you:

http://www.cpearson.com/excel/Random.htm

 

[dcardno]

1. Assign each person a random number - it can be in the range of 0-1 - and then paste those random values onto themselves
2. Sort the names by that random number and then number the names from 1 to 300 (or use RANK)
3. Use that numbering to assign to tables - as Andrew suggested, just put 1-6 at table 1, 7-12 at table 2, and so on