How to select a random percentage of a spreadsheet to remove for sample
I've got a mailing list for a client that want's to randomly remove a percentage of the data for what their calling a "dormant" list. I've tried inserting a new column "A" and using the rand formula to assign a random number but I'm having no luck. Any help would be greatly appreciated! I've attached a demo mailing list. I would like to be able to randomly remove 20% of the list and copy them to sheet2. I will be doing this on a ongoing basic so I was thinking a macro?
Excel-Demo-List.xls
Excel-Demo-List.xls
ASKER
Perfect!
Two questions.
If I need to change the percentage from 20% to 10% I change this line of the macro?:
int20Pct = Round((intLastRow - 1) * 0.2, 0) from 0.2 to 0.1?
Second question:
My list may not always be the same number of columns. Some lists may have more. What will I need to change?
Two questions.
If I need to change the percentage from 20% to 10% I change this line of the macro?:
int20Pct = Round((intLastRow - 1) * 0.2, 0) from 0.2 to 0.1?
Second question:
My list may not always be the same number of columns. Some lists may have more. What will I need to change?
ASKER
I tried to give you best solution but it wanted to close the question. I'll give you it after
1) Chang the percentage: yes, that change is correct, but you'll also have to change the RANDBETWEEN range from (1,5) to (1,10). There's another algorithm that can be used if you think you'll chang regularly where you can input the percentage at run time. Let me know if you might need that.
2) The code assumed data in columns A:G; any reference to such would have to be changed. In addition, the temporary "flag" column is in H (one column to the right); that would also need to be changed. If you need a more open-ended solution, I could revise the code to detect the columns used and run from there.
2) The code assumed data in columns A:G; any reference to such would have to be changed. In addition, the temporary "flag" column is in H (one column to the right); that would also need to be changed. If you need a more open-ended solution, I could revise the code to detect the columns used and run from there.
ASKER
You folks that help out here are amazing! This is a new customer and will have on going work with them. They will have different clients so I would imagine the parameters may vary. If you could add the open-ended I will forever be in your debt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You Rock! That worked perfect.
Thank you, thank you
Thank you, thank you
ASKER
Thank you very much Glenn.
You're Welcome, Dan. Glad I could help.
I've added code to your sample workbook that will add a temporary column with a random value generator, =RANDBETWEEN(1,5) , then iteratively recalculate the workbook until 20% of those values are equal to 5. Then it filters and copies over the items to a new sheet - [Sample] - then removes them from the first sheet.
The macro name is "Move_Entries" and can be access by pressing [Alt]+[F8] and selecting that macro from the list.
Note: this is a destructive process! The items are MOVED from the [AddressData] sheet. If you already have a [Sample] sheet of data and run the process again, the sample items are lost. Forever.
-Glenn
EE-Excel-Demo-List.xls