Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a new sheet containing random sampler 200 rows over 2000
Based on a sheet containing 2260 rows I need to create a new sheet that would
select randomly 200 rows. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a new sheet containing random sampler 200 rows over 2000
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 wrote in message ... Based on a sheet containing 2260 rows I need to create a new sheet that would select randomly 200 rows. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a new sheet containing random sampler 200 rows over 2000
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 wrote: Based on a sheet containing 2260 rows I need to create a new sheet that would select randomly 200 rows. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a new sheet containing random sampler 200 rows over 2000
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" wrote: Based on a sheet containing 2260 rows I need to create a new sheet that would select randomly 200 rows. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a new sheet containing random sampler 200 rows over 2000
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. "Thierry Sophia-Antipolis" wrote: Based on a sheet containing 2260 rows I need to create a new sheet that would select randomly 200 rows. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create groups of several rows in sheet (excel)? | New Users to Excel | |||
How do I create a random number generator in excel? | Excel Worksheet Functions | |||
How can I create a list of random numbers with no duplicates? | Excel Discussion (Misc queries) | |||
How do I create a random sample from a list? | Excel Worksheet Functions | |||
create random NON-REPEATING numbers (e.g., 20 #'s between 1-100) | Excel Discussion (Misc queries) |