Selecting a Random Sample without Replacement using Excel VBA

This video demonstrates how to select a random sample without replacement using Excel VBA. Using a selected range, the VBA subroutine will return a specified number of randomly selected rows and corresponding values without duplicates.

[nop]

This really helped get me started on a project of mine, one weakness to this method I noticed while working with it is that if you have multiple identical entries it will increase the chance of it getting picked initially but will not be picked a second time. Thankfully that didn't really matter for my use case so it got the job done but I just thought it was worth mentioning..

[rosasutton]

The technophile in me likes seeing the coding side of Excel. I like that in Excel generating random sampling is possible with coding via VBA. You explain the code in detail and don't make the assumption that all of your students are familiar with coding.

[cassiemclaughlin]

I found this extremely helpful as I do not like to have duplicates. I noticed in some of the other techniques you can have duplicates and I am not a fan. Thank you for this video!

[scottgarrett]

Great job explaining!!  Is there a way to return a random sample based on a number that you enter in a cell.  For example if you have 50 entries but you only want to return a random sample of 5.

[kyarapanula]

Clear video that I think I may be coming back to this semester!

[powerdriller]

To implement "Sampling Size n Without Replacement", VBA is the way to go to avoid even the slimiest probability of repetition. But, the alternative of using Function RAND() (to mark every data point with a 15 digit random number) works with a lurking but extremely low probability of failure (in the order of 10^ -14). It´s real disavantage is not that low probability of failure, but the amount of overhead that it generates to get a single sample. However, it does not need VBA coding, and for small data sets (below 100) is convenient; even if for paranoid people think it is like rouletteing the revolver.
--- However for applications like simulating a Lottery of 100, 000 numbers, we better use VBA, otherwise we would create a monster matrix of overhead.

[Jolicielicieux]

Thanks so much for the VBA code. As i want to generate a random sample without replacement, I copied this VBA code and applied it to my data set but when i run it I get duplicated sample. I checked the code against your code and didn't see differences. Any idea why the code would generate a duplicated sample? I have tweaked the EXCEL file which contains the data to sample and wonder whether somehow adding new data fields affects the sample generation? I still get a sample generated but sample observations are repeated. Any suggestions for debugging?

[Andromeda-twlb]

Is there a way to use Randomize to make this subroutine seeded with repeatable results for analysis?

[kimeeshareedwalker]

This part of excel seems complicated. The coding part was difficult to follow. Hands on may be more helpful.

[jaypatel]

hardly you can see please use bigger font