# Thread: sampling without replacement (2002)

1. ## sampling without replacement (2002)

I am trying to draw a sample of dates without replacement (no date can be drawn more than once). I have tried the analysis tools add-on sampling function but it does it with replacement. I have also tried the analysis tools add-on random number generator function (where I tell it to generate random dates based on my list of dates) but still I have the replacement problem. To check the resulting list of dates for duplicates I ended up using conditional formatting to yellow highlight the cell if it is identical to cell above, then sorting by date , deleting the highlighted duplicates and then sorting back to the original random order in which they were drawn. Problem is this approach is time consuming (I have 48 different samples I have to draw).

Is there a way to draw a sample of dates without replacement short of writing a program in visual basic? Thanks!

2. ## Re: sampling without replacement (2002)

Say you have dates in A1:A1000
In B1, enter =RAND()
Fill down by double clicking the fill grip in the lower right corner of B1.
Sort on column B
Select and copy the top n cells in column A.

Each time the sheet is recalculated, the random numbers will change.

3. ## Re: sampling without replacement (2002)

Another idea similar to Hans' Suggestion:
(I put the randon numbers before the dates for Vlookup)
Say you have dates in B1:B1000
In A1, enter =RAND()
Fill down by double clicking the fill grip in the lower right corner of A1.

Now if you want 48 random dates without replacement, in D148, enter in D1:
<pre>=VLOOKUP(LARGE(\$A\$1:\$A\$1000,ROW()),\$A\$1:\$B\$10 00,2,FALSE)</pre>

Copy D1 to D248

Now <f9> will constantly give you a new sample of dates. No need to sort at all or even copy/paste.

Column A could be hidden if desired. Or if you wanted to put it onto another sheet, instead of Vlookup use, match and index combination.
Steve

4. ## Re: sampling without replacement (2002)

Depending on exactly what you are trying to do, the code Here can be adapted to do it.

Legare

5. ## Re: sampling without replacement (2002)

As I see it, it is going to require VBA. The methods discussed by Hans, Steve and Legare do not address the duplicate date problem you are trying to avoid.
Someone that is better than I am with VBA (just about anyone in the lounge) could write a macro that would do this making sure he dates are unique.
Or, maybe I missed the point.

Chuck

6. ## Re: sampling without replacement (2002)

Mine and Hans do not produce duplicates. Steve's solution has a small possibility of doing so.

7. ## Re: sampling without replacement (2002)

Legare,
To Legare, Hans and Steve -MY BAD. I misunderstood and now have egg on my face. I thought there were duplicate dates in the raw data and the random number generator could still give some duplicates. But, this is not the case.

Chuck

8. ## Re: sampling without replacement (2002)

If the small possibility of dup random number is an issue, you could use something like
=int(100000*rand())+row()/100000

=rand()

which should give no dupes (but is probably not entirely random (though I don't know if the rand function is entirely random either)

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•