Random Sample Without Duplication

[Guest]

I have a macro that generates a random sample of data for me. However, I
always end up with duplicate rows. Here is my macro info:
Sub takeSample()
Randomize Timer
For SampleRow = 2 To 80
dataRow = 2 + Fix(466 * Rnd)
Worksheets("Data").Rows(dataRow).Copy
Worksheets("Sample").Rows(SampleRow).PasteSpecial
Next SampleRow
End Sub
Can anyone Help me write this so I can generate 50 samples from my list that
are not duplicated?

Thanks!

 

[JE McGimpsey]

Take a look at the method used here:

http://mcgimpsey.com/excel/udfs/samplenoreplace.html

 

[Guest]

I can't figure out how to work this into my macro. The problem is i have
data that is in 5 columns. I need random sampling without duplicating the
data in column A but the whole row needs to be brought over to worksheet two.
Thanks!!

 

[bplumhoff]

Hello,

If you take my UDF UniqRandInt() from www.sulprobil.com (or JE's
RandInt()), then you can, for example:

1. Select cells A2:A80 in sheet Sample and array-enter
=UniqRandInt(466) [enter with SHIFT + CTRL + ENTER]
2. Select cells B2:F80 in sheet Sampe and array-enter
=INDEX(Data!A2:E467,$A2:$A80,{1,2,3,4,5})

HTH,
Bernd