LinkBack URL
About LinkBacks
Hi all,
I have a column of about 120,000 numbers (A2:A120000) and would like to randomly select about 1200 of these numbers and place them in column b (B2:B1200) using VBA, any suggestions?
Cheers,
Marcus
Hi all,
I have a column of about 120,000 numbers (A2:A120000) and would like to randomly select about 1200 of these numbers and place them in column b (B2:B1200) using VBA, any suggestions?
Cheers,
Marcus
Try this in B2 then drag down to B1200
Replace 1 with the lowest # in column A and 50 with the highest # in column A
Please Login or Register to view this content.
This may only work if your numbers in column A do not skip.
This might work better for you
Please Login or Register to view this content.
Last edited by gmr4evr1; 05-25-2015 at 01:02 PM. Reason: Added alternative
1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.
Tom
Are there any duplicates in your list? Are you ok with having duplicates in the randomly selected list of 1200? The answers to those questions may impact the responses you get.
Withdrawn by FR.
Last edited by FlameRetired; 05-25-2015 at 07:20 PM.
Hi all,
gmr - I was thinking about using randombetween however what I need is a subset of the actual 120,000 numbers. The min is 0.75 and max 771 with a mean of 4.5 so I don't think Randbetween will work for me as it wont give me an accurate representation of the data.
acroley1 - yes there are duplicates and I am happy to have duplicates in the subset.
To further explain the 1200 number, this is random and depends on how many values are in a third column. So I need to do something like:
do while "third column" <> ""
randomly select a number from column A and place it in column B
loop
Cheers,
Marcus
I entered in Column A the values from 1 to 120,000
In Column B I used RANDBETWEEN(1,100,000,000) Then copied the range and pasted Values to get rid of the formula
In Column C I entered this to rank all the values in Column B without having ties:
Formula:
Please Login or Register to view this content.
In Column H I entered this to get the 1200 smallest values:
Formula:Please Login or Register to view this content.
In Column G I entered this to get the corresponding values
Formula:Please Login or Register to view this content.
This is long and involved but returned 1200 random unique values.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Thanks for the suggestion newdoverman but sleeping on it obviously helped and I cracked it with this:
Probably would have helped if I attached the worksheet with the actual information on it in the first place to help with my explanation.Please Login or Register to view this content.
Thanks all who replied.
Cheers,
Marcus
and I just spotted the obvious error and changed the false to a true!
Glad you got it figured out and thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks