How to select a random percentage of a spreadsheet to remove for sample

Posted on 2016-10-12
Last Modified: 2016-10-14
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?
Question by:Dan Purcell
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 27

Expert Comment

by:Glenn Ray
ID: 41841107
While you could add a RANDBETWEEN function to help flag 20% of the items, the issue will be that it won't always guarantee that you'll get exactly 20% identified.  That's where an iterative process in a macro comes in.  And since you want to move the items to another sheet, all the more reason for a macro.

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.


Author Comment

by:Dan Purcell
ID: 41841933

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?

Author Comment

by:Dan Purcell
ID: 41841935
I tried to give you best solution but it wanted to close the question. I'll give you it after
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

LVL 27

Expert Comment

by:Glenn Ray
ID: 41841946
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.

Author Comment

by:Dan Purcell
ID: 41842102
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
LVL 27

Accepted Solution

Glenn Ray earned 500 total points
ID: 41842886
I've modified the code to
1) allow for any percentage to be specified ("intSamplePct" at the top of the code); currently set to 20
2) allow for any size (columns) of the original data.  The temporary column is now inserted in column A and then removed.

See the revised workbook.

Author Comment

by:Dan Purcell
ID: 41843608
You Rock! That worked perfect.

Thank you, thank you

Author Closing Comment

by:Dan Purcell
ID: 41843609
Thank you very much Glenn.
LVL 27

Expert Comment

by:Glenn Ray
ID: 41844407
You're Welcome, Dan.  Glad I could help.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question