Taking a random sample from a population of names/contacts

**whitnd4** · 08-19-2009, 11:26 AM

So I have a list of over a thousand people and I need to take a random sample of 50 or so to include in an e-mail blast.

Each distinct row corresponds to a certain person with corresponding demographical information in each column. I would like to randomly choose a certain number of rows and have them copied and compiled into a new worksheet.

If possible I'd like to be able to do this without macros as my VBA knowledge is limited and I'd like to be able to easily adapt the process for different population and sample sizes.

Can anyone help me out witht his problem? Thanks!

JD

**whitnd4** · 08-19-2009, 11:34 AM

Here is an example workbook for the problem. For this example say I want a random sampling of 5 rows on the second sheet in this workbook. I also need the sampling to take place without replacement so there will never be duplicates. Thanks again

**shg** · 08-19-2009, 11:56 AM

You could do it like this:

       ---A--- ------B------- --C--- ------------D-------------
   1               People     Choose                           
   2                 20         5    B2: =COUNTA(B3:B65536) - 1
   3    First       Last      Chosen C2: Input                 
   4       Tom Brady                                           
   5     Brett Favre                                           
   6    Peyton Manning          x                              
   7   Donovan McNabb                                          
   8   Michael Vick             x                              
   9      Kurt Warner                                          
  10       Eli Manning                                         
  11       Ben Roethlisberger                                  
  12    Carson Palmer           x                              
  13      Matt Hasselbeck                                      
  14     Trent Green                                           
  15      Jake Plummer          x                              
  16      Drew Brees            x                              
  17     Brian Greese                                          
  18      Chad Pennington                                      
  19      Mark Brunel                                          
  20       Jay Feely                                           
  21     Aaron Brooks                                          
  22   Phillip Rivers                                          
  23       Jon Kitna

The formula in C4 and down is

=IF(RAND() < ( C$2 - COUNTIF(C$3:C3, "x") ) / (B$2 - ROWS(C$3:C3) + 1), "x", "")