Selecting Random Records From an Excel Sreadsheet

[Trevor Aiston]

I have a workbook which contains 300 records from which I wand to be able to
randomly select 50 records. Each record has a geographical area attribute
(e.g. east, west etc) and I have worked out proportionality how many per
geographical area I want to select, but not sure how to randomly select, e.g.
3, from each subset of data. Hope this is clear.

Any suggestions

Trevor

 

[Lars-Åke Aspelin]

I have a workbook which contains 300 records from which I wand to be able to
randomly select 50 records. Each record has a geographical area attribute
(e.g. east, west etc) and I have worked out proportionality how many per
geographical area I want to select, but not sure how to randomly select, e.g.
3, from each subset of data. Hope this is clear.

Any suggestions

Trevor

Here is one way you may try:

Assuming that your data are on row 1 to 300 and that the B column
holds the geographichal area and that there are at least 3 records for
each geographical area.

Also assuming that you have two free columns that can be used as
helper columns. In this example the columns G and H are used.

In cell G1 you put the formula:

=RAND()

Copy the formula to cells G2:G300 and then
Copy and "Paste special/Values" the range G1:G300 to itself.

In cell H1 you put the formula:

=IF(G1>=LARGE((B$1:B$300=B1)*(G$1:G$300),3),"Selected","")

Note: This is an array formula and has to be entered by
CTRL+SHIFT+ENTER rather than just enter.

Copy the formulas to cells H2 to H300.

Column H should now have the value "Selected" for three records for
each geographical area. You can use autofilter to display just the
selected record and maybe copy them elsewhere for further processing.

Hope this helps. / Lars-Åke

 

[Joel]

thge problem with random slection is if two number have the same RANK to only
select one of the numbers and not to select the same number twice. the best
way to aviod this problem is to assign a random number ot each item and then
perform a sort. I would assign an ID number for each record and then create
a lookup table for determining the random records you want.

First build a look up table for the geography areas and the number of
records you want from each area.

row column column
S T
2 Area Qnty
3 East 3
4 West 2



for random lookup table we will use the row number of record as the ID

row column column column
X Y Z
1 ID Region Random Number
2 1 East =rand() .234
3 2 West =rand() .456
4 3 South =rand() .214


1) Now copy column Z and Paste Special back into column Z to remove formula
2) Sort data in columns X-Z by Random number
3) You want to put either a true of false in a new colum of your original
record table that contains a True or False. Lets make this new column D.
Column B contains the region
=lookup(B1,S$1:T$100,2) to get the number of items selected from each
region

use this formula to get the index of the item in the random table in column
X to Z

=match(row(),X:X)

Now to get the rank of the item in the table for each region

=countif(indirect("X1:X" & match(row(),X:X)),B1)

The above formul will tell you if the item is the 3rd random item in the
East region


4) Now you want to compare the results of the above formula with the number
of items you want in each region. You need to comibine these two formulas
using an IF function

=lookup(B1,S$1:T$100,2)

and

=countif(indirect("X1:X" & match(row(),X:X)),B1)


results

=if(countif(indirect("X1:X" &
match(row(),X:X)),B1)<=lookup(B1,S$1:T$100,2),True,False)

 

[Shane Devenshire]

Hi,

1. To do random sampling with replacement try the Data Analysis tool Sampling:
a. choose Tools, Add-ins, and check Analysis ToolPak.
b. choose Tools, Data Analysis, Sampling...

2. To do random sampling without replacement:
a. Enter =RAND() in a column adjacent to the data
b. Sort the data based on that column
c. Pick the first n items.