Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

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.