Random Copy and Paste

[Guest]

Okay this one is a bit tricky. I have read several of the posts on random
number generation, but still can't quite put this one into perspective. In
column K I have a variable number of records anywhere from 1 to X. It will
change each time I run the function. The records are grouped by a prefix 1-6
(ie. 6-2034, 2-3905). The thing is that I need a random sampling of 30 of
those records to be pasted into column A. Then it only gets trickier.

Each prefix must be represented in column A dependent upon how much it shows
up in column K with at least one of each prefix being copied if it exists in
K. For example lets say that there are 100 records in K: 59 of them have the
1 prefix, 25 of them have the 2 prefix, 10 of them have the 3 prefix, 5 of
them have the 4 prefix, 1 of them has the 5 prefix and 0 of them have the 6
prefix. In the 30 record sample I would need the following sampling:
Prefix--Sample
6--0
5--1
4--2
3--3
2--8
1--16

I actually have a little summary box on the sheet that tells me how many
samples that I need using the following formula: =IF(B7=1, 1,
PRODUCT(B7/B13*30)) B7 being the number of times that prefix shows up in
column K and B13 the total of all prefixes in column K. Prefix 1 is always
the most prevelant and so its formula is simply 30 - the total sampling of
all other prefixes (I have to do this in order to keep the sample at 30).

So basically, in this sample I would need to randomly choose 16 numbers in
the K column that have the 1 prefix and paste them to a table I have in
column A, 8 random ones for 2 prefix and so on. Obviously for prefix 5 the
one number in column K that has the 5 prefix would be copied.

The range of the numbers in column K are K3-Kx
The range where the random numbers will be pasted in column A are A17-A46

I hope this makes sense. It's hard to explain without being able to show
the worksheet.

Thanks in advance for any help

 

[sulprobil]

You will find some help on
http://www.sulprobil.com/html/random_numbers.html, i think:

1. Sort your values in col K, lets say in L.
2. For each different occuring prefix pick 1 random
instance and delete it in L.
3. Now for each occurence Oi of each prefix pick
UniqRandInt(Int(count of numbers of Oi in L / count of all
numbers in L * (count of your desired sample - earlier
picked count)). You will have to adjust the function to
cope with the implicit parameter (define it explicitly)
because its not called from within the worksheet but in
VBA. Delete all picked values in L.
4. If you have not reached your desired number of samples
so far, pick them randomly from L and delete them in L.

HTH,
sulprobil

 

[sulprobil]

No, step 2 ensures that you will get 1 example for each
existing prefix. If you pick a value then you positively
choose it. You delete it in col L so that you cannot pick
it twice.

Its only a rough description of a possible approach.

Regards,
sulprobil