Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Copy and Paste
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Copy and Paste
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Copy and Paste
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Copy; Paste; Paste Special are disabled | Excel Discussion (Misc queries) | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
I cannot paste from one workbook to another. Copy works, paste do. | Excel Discussion (Misc queries) | |||
Macro to Copy/Paste then Paste to Next Line | Excel Programming |