Create a new sheet containing random sampler 200 rows over 2000

[Thierry Sophia-Antipolis]

Based on a sheet containing 2260 rows I need to create a new sheet that would
select randomly 200 rows.

 

[Bernard Liengme]

If you want to do this without using VBA:
1) On Sheet2 in A1 enter =RANDBETWEEN(1,2260) and copy down to row 200
This function needs the Analysis Toolpak; if it is not installed use
=INT(RAND()*199+1)
2) In B1 enter =INDIRECT("Sheet1!R"&$A1&"C"&COLUMN()-1,FALSE)
3) Copy this across the row; suppose on Sheet1 the last column is P, then
copy to Q
4) Copy down to row 200

Of course, you could but the random number in column Z, then in A1 use
=INDIRECT("Sheet1!R"&$Z1&"C"&COLUMN(),FALSE)

If you want the data to become static; Copy all of Sheet2 other than column
A and use Paste Special with Values specified.

As with all random number system, you may get he same row selected more than
one. If this must be avoided, you need VBA. Google with "Excel random
unique"
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Thierry Sophia-Antipolis" <Thierry
(e-mail address removed)> wrote in message
news:[email protected]...

 

[Chip Pearson]

Try some code like the following:

Sub AAA()
Dim V As Variant
Dim N As Long
Dim StartRandList As Range
Dim StartOrigData As Range

' StartRandList is the first cell where the random rows
' are to be written.
Set StartRandList = Worksheets("Sheet2").Range("A1")

' StartOrigData is the first cell of the data to be
' randomly copied.
Set StartOrigData = Worksheets("Sheet1").Range("A1")

V = UniqueRandomLongs(Minimum:=1, Maximum:=2260, Number:=200)
For N = LBound(V) To UBound(V)
StartOrigData(V(N)).EntireRow.Copy
Destination:=StartRandList(N, 1)
Next N
End Sub


You'll need the modRandomLongs module from
http://www.cpearson.com/zips/modRandomLongs.zip
which contains the UniqueRandomLongs function.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 26 Mar 2009 03:22:03 -0700, Thierry Sophia-Antipolis <Thierry

 

[Mike H]

Hi,

Right click the sheet tab with the 2260 rows in, view code and paste this
in. Change srcsheet to your sheet name. It creates a new sheet with 200
random rows in

Sub Liminal_Advertising()
Dim copyrange As Range
srcsheet = "Sheet1"
Dim FillRange As Range
Set wks = Worksheets.Add(after:=Worksheets(Worksheets.Count))
wks.Name = "Random Selection"
Set FillRange = Sheets("Random Selection").Range("A1:A200")
For Each C In FillRange
Do
C.Value = Int((2260 * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(FillRange, C.Value) < 2

If copyrange Is Nothing Then
Set copyrange = Sheets(srcsheet).Rows(C).EntireRow
Else
Set copyrange = Union(copyrange,
Sheets(srcsheet).Rows(C).EntireRow)
End If
Next
copyrange.Copy Destination:=Sheets("Random Selection").Range("A1")
End Sub


Mike

 

[Thierry Sophia-Antipolis]

Thanks to the forum I have found the solution.
Create a new column, fill it with =RAND() then sort the column with Top 200
Items.