Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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:

 NO     NAME    AMOUNT
 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:

 NO     NAME    AMOUNT
 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
2  
Possible duplicate of MS Excel: Unique random number generator within a range – Raystafarian Apr 18 at 17:14
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 ozgrid.com 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .