Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have an Excel sheet as follows:

 1       A        50
 1       B        50
 2       A       100
 2       C       100
 3       D        70
 3       B        70
 4       A        30
 4       F        30
 5       C       150
 5       G       150

There are let's say 10,000 rows.

I want to get a random sample from rows. There are 2 conditions:

1. Sampling must be based on "NO" column.
2. Size of the sample is determined by the user: it can be %5, %10 or %20.

For example, one decides to randomly choose %20 of total rows in the above example:

The result is like:

 2       A       100
 2       C       100
90       Z       500
90       E       500

There should be 2,000 rows.

I don't know whether my question is too specific. I am new to Excel VBA, and I faced a situation like this. Above process is about getting a random sample from an account ledger for auditing purposes.

share|improve this question
up vote 2 down vote accepted

I think your question boils down to generating the sample set: there is an example of doing this at which can be adapted to return a set of row numbers. It creates an array of row numbers, randomises them by shuffling, then return the required number of samples from the top of the array.

Once you have your sample set, its a case of pulling out the sampled rows (auto filter, copy to another sheet, or whatever)

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.