Solved

# Random classification of records

Posted on 2014-04-07
Medium Priority
101 Views
Hello, I would like to randomly classify each of the attached 201 data records into one of 5 groups. Four of the groups will contain 40 records, and a fifth group will contain 41 records.

The group number (1-5) should be contained in column C.

Does anyone have a suggestion on how to randomly generate these five groups?

RandomClasses.xlsx
0
Question by:dougf1r
• 5
• 3

LVL 7

Expert Comment

ID: 39984338
if you place the formula in a spare column and recalc the sheet, you should be close.
RANDBETWEEN( 1, 5 ),
you should then sum each assignment (1-5) to ensure you have a balanced distribution.
0

LVL 1

Author Comment

ID: 39984352
How can this be modified such that I have 4 equal-sized groups (n=40) and one group with n=41?
0

LVL 7

Expert Comment

ID: 39984362
Without running a small macro, populating each cell, and keeping a running total, I don't know. The above will be close.
Do you have to repeat this, or is it a once-off?
0

LVL 1

Author Comment

ID: 39984393
I will need to repeat this.

A small macro does seem that it would be most helpful. I am unsure of the proper code to carry this out.

Might someone be comfortable putting this code together?
0

LVL 7

Expert Comment

ID: 39984467
In Excel, install the Analysis toolpack -VBA from Tools Addins.
Then in VBA, click Tools - References and put a checkmark on atpvbaen.xls
The problem with this is if you share your book, the other person must also have the analysis toolpack-vba addin installed.

Sub PopVals()
Dim vval As Long
Dim counts(5) As Long
x = 1
While x <= 201
vval = WorksheetFunction.RandBetween(1, 5)
Worksheets("Sheet2").Cells(x, 3) = vval
vval = vval - 1
If counts(vval) < 51 And vval < 5 Then
counts(vval) = counts(vval + 1)
x = x + 1
End If
If counts(vval) < 52 And vval = 5 Then
counts(vval) = counts(vval + 1)
x = x + 1
End If
Wend

End Sub
0

LVL 7

Expert Comment

ID: 39984485
The above isn't correct - please wait for a better response - I have to go.
0

LVL 7

Accepted Solution

COACHMAN99 earned 2000 total points
ID: 39984747
Here you go:
Sub PopVals()
Dim vval As Long
Dim counts(5) As Long
x = 1
Range("C1:C201") = ""
While x <= 201
vval = WorksheetFunction.RandBetween(1, 5)
Worksheets("Sheet2").Cells(x, 3) = vval
vval = vval - 1
If counts(vval) < 40 And vval < 4 Then
counts(vval) = counts(vval) + 1
x = x + 1
End If
If counts(vval) < 41 And vval = 4 Then
counts(vval) = counts(vval) + 1
x = x + 1
End If
Wend

End Sub
0

LVL 1

Author Comment

ID: 39990166
Works perfectly. Many thanks!
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.