random sample

[Lisa2009]

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"><tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"><td>A</td> <td>B</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td style="text-align: center;">employee #</td> <td style="text-align: center;">ticket#</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="text-align: center;">12345</td> <td style="text-align: center;">a123</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="text-align: center;">54231</td> <td style="text-align: center;">w234</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="text-align: center;">23654</td> <td style="text-align: center;">23tyu</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="text-align: center;">62547</td> <td style="text-align: center;">fe33</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="text-align: center;">54789</td> <td style="text-align: center;">c865</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="text-align: center;">12345</td> <td style="text-align: center;">a123</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="text-align: center;">12345</td> <td style="text-align: center;">w234</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td style="text-align: center;">23654</td> <td style="text-align: center;">fe33</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td style="text-align: center;">62547</td> <td style="text-align: center;">c865</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td style="text-align: center;">54789</td> <td style="text-align: center;">c865</td></tr></tbody></table>
I have data like above with more data in coulmns A to z and rows upto 65k. Is a macro possible that will pick out 25 random rows for each employee# and not repeating the sameticket#. Pick out the random sample and save in a new file on the desktop,with file name 'sample', or atleast just open in a new file ,if saving and naming file is too much.

 

[Lisa2009]

anyone ?

 

[ajetrumpet]

this is how you can randomize 10 cells in Column 'A' with an array:

Sub MixItUp()

Dim i As Integer
  Dim int1 As Integer
    Dim int2 As Integer
      Dim strVAR As String
        Dim var(10) As Variant

  For i = LBound(var) To UBound(var)
    
    var(i) = i
  
  Next i

Randomize

    For i = LBound(var) To UBound(var)

      int1 = Int(Rnd * (UBound(var) - LBound(var) + 1))
      int2 = Int(Rnd * (UBound(var) - LBound(var) + 1))

        While int1 = int2
          int2 = Int(Rnd * (UBound(var) - LBound(var) + 1))
        Wend

      strVAR = var(int1)
      var(int1) = var(int2)
      var(int2) = strVAR

    Next i

  For i = LBound(var) To UBound(var)

'PRINT THE CELL VALUES HERE TO SEE THE RANDOMIZATION AND NO REPEATS

    Debug.Print Cells(var(i) + 1, 1)
    
  Next i

End Sub

You can use this and basically do whatever you want with the value right after the 2nd FOR - NEXT loop. The 3rd is for display purposes only. After the second loop, the number of records you want randomized will already be in place, and ready to be manipulated.