MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Selection by column max of 5

Posted by Ken on April 09, 2001 6:56 AM

I have multiple records with a column with user id's. I would like to randomly pick 5 records for each user. Thanks


Posted by Dave Hawley on April 10, 2001 12:21 AM

Hi Ken

Here is some code I have that will create a table of randomly generated numbers between 1 and 36 in range A1:F6 of the activesheet. You should able to adapt it to your needs.

Sub RandomNumberGenerator()
'Creates a list of random numbers _
between 1 and 36 in range A1:F6
'Written by OzGrid Business Applications
Dim Rw As Integer, Col As Integer
'Clear the range ready for random numbers
Randomize ' Initialize random-number generator.
For Col = 1 To 6 'Set the Column numbers
For Rw = 1 To 6 'Set the Row numbers
Cells(Rw, Col) = Int((36 * Rnd) + 1)
Do Until WorksheetFunction.CountIf _
(Range("A1:F6"), Cells(Rw, Col)) = 1
Cells(Rw, Col) = Int((36 * Rnd) + 1)

Next Rw
Next Col
End Sub


OzGrid Business Applications