Hi all,
Is there a way we can randomly and uniquely select or pick 10% data from 1000 of records ?
Example : i have lets for example 100 records out of which i want to select randomly 10% of the data and do the analysis i.e 10 records from each Month and each country .
attach is the example for your reference.
Can you explain what you mean by "uniquely"?
BSB
Hey BSB, you can ignore that .. that does not really matters .
thanks.
One simple way would be a helper column.
Put this formula in F2:
Formula:
Please Login or Register to view this content.
Put this formula in G2:
Formula:Please Login or Register to view this content.
Copy down down as necessary.
Now in column F there will be 10% of entries with the word Audit picked at random (well as random as Excel can be).
Note that making any cell changes will refresh the randomise so you may want to either switch off auto-calculation or paste values over column F after you've generated your random values.
BSB
This is great , but does the 10% evenly from all the regions and months ?.
I want parameters to be taken care like (region and month ) , make sense ?.
Am i expecting too much ?. i know for 100 records it is very simple but i will be have more than 10,000 records ..
Last edited by hudson andrew; 01-15-2018 at 10:36 AM. Reason: more details
The following proposed solution employs two helper columns.
The first helper column (H) is populated using: =RAND()
The second helper column (I) is populated using: =1+SUMPRODUCT((D$2:D$100=D2)*(E$2:E$100=E2)*(H$2:H$100>H2))
Column F is populated using: =IF(I2<=ROUNDUP(COUNTIFS(D$2:D$100,D2,E$2:E$100,E2)/10,0),"Audit","")
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks