Hi,
I have a long list of rows, and what I would like to is to randomly select 60 rows from the long list with a click of a button.
I'm not sure what the best approach would be.
Can anybody shed some light on this please?
Please see the attached.
Thank you
Lifeseeker,
Attached is a modified version of your example workbook. I copied the list of IDs to Sheet2, column A. In Sheet2, column B I then entered the formula =RAND(). Then I set Sheet2 to hidden.
Here's the macro assigned to the button:
Please Login or Register to view this content.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Oh wow. Thank you
Could you explain this part please?
Does "randomize" do the randomizing by default? The Intersect() is looking for the intersection between the used range and A and B in Sheet2?Please Login or Register to view this content.
Randomize shuffles Excel's randomization functions to start at a new location. After that, the .Calculate is what's causing the =RAND() functions to pull new random numbers, based on the current location of Excel's randomization table. In Excel's help file [F1], the Randomize Statement is defined as: "Intitializes the random-number generator."
the Intersect() method catches all items in columns A and B that are in sheet 2's used range. Note that this isn't strictly necessary because the only items in sheet2's used range are columns A and B. If you ever updated sheet2 with extra info in the other columns though, it wouldn't upset the macro because its using intersect.
THank you.
I would like to put the result to a new workbook or a new sheet within the active workbook. How would I do so?
To a new sheet in the same workbook:
Please Login or Register to view this content.
To a new workbook:
Please Login or Register to view this content.
I want to also keep the column headings on the new workbook.
Also, even though I understand that all records are shuffled randomly, but how do I make sure that I'm NOT always picking from the first 60?
Is the fact that we are always picking from the first shuffled 60 suggesting that it's somewhat not random? Do you know what I mean? Because we are ALWAYS picking the first 60.Please Login or Register to view this content.
What if I just wanted to just randomly pick 60 anywhere on the list. (not necessarily the first 60) you know? Is this the same as what you have basically helped me with?
This will put the header in A1, and the chosen 60 in the rest:
Please Login or Register to view this content.
As for your question regarding the "top 60" versus "choose from anywhere":
Imagine a deck of cards and you want 30 of them at random. You shuffle the cards well and then take the top 30 cards. Since its been well shuffled, the cards could have come from anywhere (example: what was originally the bottom card could now be the top card), so taking the top 30 is exactly as random as taking out 30 cards from random positions within the shuffled deck. There's nothing inherently less or more random about either scenario.
Makes sense.
I have ran into a small glitch, and please see the attached.
On the output file, the rand numbers are in column E, which I would like to show the actual E column values from the original worksheet.
code is:
In the new workbook, col E is supposed to be showing the time, but it's showing the rand() function numbers. I'm not getting any syntax error.Please Login or Register to view this content.
Thank you
Lifeseeker,
Attached is a modified version of the new sample workbook. I included the headers in Sheet2 since there was more than one. I also changed the code a bit to do a copy/paste instead of importing values. This will allow the time values you have in column E to be formatted as dates instead of general so that they will display properly. It also pastes the column widths to provide a smooth transition. Here's the updated code:
Please Login or Register to view this content.
Ah I see.
I guess it's always safe to use the
Thank you for explaining the code thoroughly.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks