Solved

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

Posted on 2011-03-25
8
276 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
  • 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
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…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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 …

813 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now