Solved

Easy way to get a random sample of data in Excel 2007

Posted on 2011-03-25
8
278 Views
Last Modified: 2012-05-11
Hello:

I have a large data set in excel and I would like to know the quickest (and easiest) way to get a random sample of the data.

The data has several different usernames in a column called "Assigned to" and I need to get 10 random records from each user.  

Thank you in advance.

0
Comment
Question by:MeowserM
[X]
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
  • 4
  • 4
8 Comments
 
LVL 29

Accepted Solution

by:
leonstryker earned 500 total points
ID: 35215661

Sort by usernames. Use the =RANDBETWEEN() function to generate a random number 10 times and pick those rows as your sample.
0
 

Author Comment

by:MeowserM
ID: 35215746
Ok that sounds good.  Question for you:  How do I get the bottom and top numbers for each user?

I created a counter for each record within that users "group" of records.  But how do I get the MIN and MAX of that counter?

Thank you for you help
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 35215784
Once you sort your data, then MIN number will be the row where that user's data starts and the MAX number will be where that user's data stops.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:MeowserM
ID: 35215843
Yes and the MIN will always be 1 because that's where I start my counter.  But is there a formula or function I can use to determine the MAX?

0
 
LVL 29

Assisted Solution

by:leonstryker
leonstryker earned 500 total points
ID: 35215935
The MIN should not always be 1 if it is a sorted list. You can use =COUNTIF() funtion to find how many rows there are for each username, then the MAX will be equal to the MIN + COUNTIF(username)
0
 

Author Comment

by:MeowserM
ID: 35216013
Very smart.  That's what I was looking for.

 Thank you I need some extra help today.

0
 

Author Closing Comment

by:MeowserM
ID: 35216021
Thank you again.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 35216037
Thanks for the grade.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

732 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