Excel Macro to fetch random data from column A

Question

I have a list of data in column A and the same has repeated data too. I have to create a macro to get 10% of that data. I tried with Rand() and few other functions but no success. Please any help.

Column A

Brick John

Brick Matt

Brick Ted

Goal John

Goal Matt

Goal Ted

Goal Ted

Tiger Ted

Tiger John

Tiger Matt

Tiger Matt

Code Ted

Code Matt

Code Matt

Code Ted

Code John

Now from the above list I have to get random sample of 10% it is ok if I get duplicates to but I have to get from all brick, tiger, code and goal.

Please help

Please add the code you have written to your question so we can help figure out why it isn't working. — Ross McConeghy, Aug 21 '14 at 16:53

Gary's Student · Accepted Answer · 2014-08-22T12:23:42.883

This will sample and place the samples in column B

Public Sub scramble(InOut() As String)
Dim I As Long, J As Long, Low As Long, Hi As Long
Dim Temp

ReDim Helper(LBound(InOut) To UBound(InOut)) As Double
Randomize
Low = LBound(Helper)
Hi = UBound(Helper)
For I = Low To Hi
    Helper(I) = Rnd()
Next

J = (Hi - Low + 1) \ 2
Do While J > 0
    For I = Low To Hi - J
      If Helper(I) > Helper(I + J) Then
        Temp = Helper(I)
        Helper(I) = Helper(I + J)
        Helper(I + J) = Temp
        Temp = InOut(I)
        InOut(I) = InOut(I + J)
        InOut(I + J) = Temp
      End If
    Next I
    For I = Hi - J To Low Step -1
      If Helper(I) > Helper(I + J) Then
        Temp = Helper(I)
        Helper(I) = Helper(I + J)
        Helper(I + J) = Temp
        Temp = InOut(I)
        InOut(I) = InOut(I + J)
        InOut(I + J) = Temp
      End If
    Next I
    J = J \ 2
Loop
End Sub


Sub samples()
Dim N As Long, N2 As Long, s() As String
N = Cells(Rows.Count, "A").End(xlUp).Row
N2 = N / 10
ReDim s(1 To N)

For I = 1 To N
  s(I) = Cells(I, 1).Value
Next I

Call scramble(s)

For I = 1 To N2
  Cells(I, 2).Value = s(I)
Next I
End Sub

Excel Macro to fetch random data from column A

1 Answers1