Solved

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

Posted on 2011-03-25
278 Views
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.

0
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
• 4
• 4

LVL 29

Accepted Solution

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

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

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

Author Comment

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

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

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

Thank you I need some extra help today.

0

Author Closing Comment

ID: 35216021
Thank you again.
0

LVL 29

Expert Comment

ID: 35216037
0

## Featured Post

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 …