On Mon, 29 Jun 2009 02:30:01 -0700, Trevor Aiston
wrote:


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),"Selec ted","")

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