ianmiller999

Ok I have to take a random data sample say 200 people from a a excel file with about 1000 people. Is there any simple code to this or has anyone already created a macro to do this all will it be quicker for me to manually randomly select data.

markr1963

Attach this bit of code to a macro

For i = 1 To 200
j = Int((1000 * Rnd) + 1)
Cells(i, 5).Value = Cells(j, 1)
Next i

It takes 200 random values from column A and puts them in column E. Change the code to suit.

Its cheap and cheerful but should work.

Edited to add: I just spotted a flaw - duplicates! I'll try and sort that out later.

Mark

[Edited by markr1963 - 10/15/2003 11:04:01 AM]

markr1963

Had another go.

Dim a(1000) As Variant

For i = 1 To 200
redo:
Randomize
j = Int((1000 * Rnd) + 1)

For k = 1 To i ' checks for match in array
If j = a(k) Then ' if there's a match generate another number
GoTo redo
End If
Next k
Cells(i, 5).Value = Cells(j, 1)
a(i) = j ' adds value generated to array
Next i

Mark

ianmiller999

Thankyou