Random Generator Question

[Simon77]

Slight query that maybe somebody could help me with.

I have 1200 Rows of data, consisting of Details of age, sex, height & weight
of people in my university.

I would like excel to randomly pick out 200 entries and place them in a new
worksheet, so that I can analyse the data & produce graphs. each entry would
include all of the above details.

Anybody know a way I can do this ?

Help would be greatly appreciated


TIA

Simon

 

[L. Howard Kittle]

Hi Simon,

In the first column to the right of your data enter =RAND(). Now pull this
formula down to the bottom of your data.

Sort the data using the rand column as the sort column. Pick the first 200.

If you want another sample then hit F9 and resort, using the first 200
again.

HTH
Regards,
Howard

 

[Dave Peterson]

I'd insert a helper column and put =rand() in the first row and copy down. (I'd
put a formula in column A that returns the original sort order =row() and then
convert that to values.)

Then I'd sort by that column and copy the top 200 names to the new sheet.

Then put the list back in the original order and clean up the helper columns.

As a macro it would look like:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim newWks As Worksheet
Dim rngToCopy As Range
Dim LastRow As Long
Dim dummyRng As Range

Set wks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("a:b").Insert
.Range("b1:b" & LastRow).Formula = "=rand()"
With .Range("a1:a" & LastRow)
.Formula = "=row()"
.Value = .Value
End With
.Range("a1:c" & LastRow).Sort _
key1:=.Range("b1"), order1:=xlAscending, header:=xlYes
Set rngToCopy = .Range("c1:c201") 'includes header
rngToCopy.Copy _
Destination:=newWks.Range("a1")
.Range("a1:c" & LastRow).Sort _
key1:=.Range("a1"), order1:=xlAscending, header:=xlYes
.Range("a:b").Delete
Set dummyRng = .UsedRange 'try to reset lastcell
End With
End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Max]

Another option to quickly set-up & play around with?

In Sheet1
------------
Assume your data-set is in A11201, row2 down
(sample below)

Age...Sex...Height...Weight
24......M....192.........69
35......F......161.........59
31......F......189.........74

Fill down E2:E1201 with the numbers 1 – 1200

Put in F2: =RAND()
Copy down to F1201

Name the range F2:F1201 as say: RandTable

In Sheet2
-------------
Copy > Paste over the col headers in row1 (A11):

Age...Sex...Height...Weight

Select E2:E1201

Put in the formula bar: =RANK(RandTable,RandTable)
Hold down Ctrl & Shift, press Enter (it's an array formula)
(instead of just pressing Enter key alone)

Done correctly, Excel will wrap curly braces around
the formula: {=RANK(RandTable,RandTable)}
(don’t type-in the curly braces)

Put in A2:
=OFFSET(Sheet1!$E$1,MATCH($E2,Sheet1!$E:$E,0)-1,COLUMN()-5,1,1)

Copy A2 across to D2, then copy down to row 201

A2201 will give you 200 unique random samples
from the 1200 in Sheet1

Just copy > paste special > values to another sheet
to freeze this random sample for your analysis / graphicals

Or, you could in fact set-up your analysis formulae & graphs
to read direct from / link to Sheet2’s A2201,
which will then allow you to see how the analysis/graph outputs
vary with various random sample-sets generated
(Pressing F9 will recalculate / generate another random set)

And if desired, the random sample size can also
be easily increased to say 500,
by just copying A22 down to row 501 instead of row 201

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email