Randomly Select Records
 

I need a way to randomly select 400 records from a worksheet containing
around 2000. Is there a formula or function that will do this?

Randomly Select Records
 

Say your data is in A1 thru A2000

1. in B1 thru B2000 enter:
=RAND()
2. sort columns A & B by column B
3. pick the first 400 items in column A
--
Gary''s Student
gsnu200710


"Critzy" wrote:

I need a way to randomly select 400 records from a worksheet containing
around 2000. Is there a formula or function that will do this?

Randomly Select Records
 

When I use your formula, it is generating only large decimal numbers that
aren't sorting correctly. Is there something to add to the formula so that
it will generate whole numbers only?

"Gary''s Student" wrote:

Say your data is in A1 thru A2000

1. in B1 thru B2000 enter:
=RAND()
2. sort columns A & B by column B
3. pick the first 400 items in column A
--
Gary''s Student
gsnu200710


"Critzy" wrote:

I need a way to randomly select 400 records from a worksheet containing
around 2000. Is there a formula or function that will do this?

Randomly Select Records
 

Whole numbers would be bad. Here is a 10 item example (instead of 2000) in
A1 thru B10:

first record 0.101136315
second record 0.261871674
third record 0.256612485
fourth record 0.37613248
fifth record 0.869187556
sixth record 0.601846916
seventh record 0.316891978
eighth record 0.239924396
nineth record 0.022173703
tenth record 0.414456984


and after the sort:

nineth record 0.022173703
first record 0.101136315
eighth record 0.239924396
third record 0.256612485
second record 0.261871674
seventh record 0.316891978
fourth record 0.37613248
tenth record 0.414456984
sixth record 0.601846916
fifth record 0.869187556


As you see the order of records in column A is randomized. Just get the top
4 or 400 for your sample.
--
Gary''s Student
gsnu200710


"Critzy" wrote:

When I use your formula, it is generating only large decimal numbers that
aren't sorting correctly. Is there something to add to the formula so that
it will generate whole numbers only?

"Gary''s Student" wrote:

Say your data is in A1 thru A2000

1. in B1 thru B2000 enter:
=RAND()
2. sort columns A & B by column B
3. pick the first 400 items in column A
--
Gary''s Student
gsnu200710


"Critzy" wrote:

I need a way to randomly select 400 records from a worksheet containing
around 2000. Is there a formula or function that will do this?

Randomly Select Records
 

One variation to play with ..

Assume your source records is in A1:A2000

Place in B1: =RAND()
Copy down to B2000

Then place in say, C1:
=INDEX(A:A,RANK(B1,$B$1:$B$2000))
Copy C1 down by 400 rows to C400

C1:C400 will return 400 random, non-duplicating picks
from the source within A1:A2000

Press F9 to re-generate
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Critzy" wrote:
When I use your formula, it is generating only large decimal numbers that
aren't sorting correctly. Is there something to add to the formula so that
it will generate whole numbers only?

Randomly Select Records
 

GREAT solution...You don't even have to sort the original data.
--
Gary''s Student
gsnu200710


"Max" wrote:

One variation to play with ..

Assume your source records is in A1:A2000

Place in B1: =RAND()
Copy down to B2000

Then place in say, C1:
=INDEX(A:A,RANK(B1,$B$1:$B$2000))
Copy C1 down by 400 rows to C400

C1:C400 will return 400 random, non-duplicating picks
from the source within A1:A2000

Press F9 to re-generate
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Critzy" wrote:
When I use your formula, it is generating only large decimal numbers that
aren't sorting correctly. Is there something to add to the formula so that
it will generate whole numbers only?

Randomly Select Records
 

Thanks for the feedback, GS !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gary''s Student" wrote:
GREAT solution...You don't even have to sort the original data.
--
Gary''s Student
gsnu200710