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

Ken


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
'www.ozgrid.com
Dim Rw As Integer, Col As Integer
'Clear the range ready for random numbers
Range("A1:F6").Clear
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)
Loop

Next Rw
Next Col
End Sub


Dave


OzGrid Business Applications