Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT- Random Sample per range of data
I am trying to create a function (?) that will allow me to get a specific
number of randomly selected cells out of a range. I know this is available, but I can't seem to get it to do the following: BACKGROUND- I have a spreadsheet that has employee names for the column titles, and training module names for each row. The connecting cells indicate the date which these training modules were completed [ex- Joe Shmoe (column title) was training on Beancounting (row title) on 7/7/77 (coordinating cell data).] PROBLEM- I need to randomly select 315 cells out of a range of employees/training modules (for example, range B2-AF247). I know this can be accomplished through the analysis tool, but I can't seem to get it to simply HIGHLIGHT THE RANDOMLY SELECTED CELLS within the actual spreadsheet. Instead, it seems to only pull the actual data out of the cells and throw it somewhere else, which prevents me from having any reference as to the employee and training module. SOLUTION- ??? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT- Random Sample per range of data
Tom,
I've learned how to apply the process, and it works fantastically, thank you!!! I am now wondering if there's a way to list those selected boxes (NOT the data contained in the boxes, but the actual cell #, such as A:220) in a separate column on a separate spreadsheet. "Tom Ogilvy" wrote: Sub ABCD() Dim rng As Range Dim rng1 As Range Dim num As Long Dim l As Long, i As Long num = 315 Set rng = Range("B2:AF247") rng.Interior.ColorIndex = xlNone i = 0 Do While i < num l = Int(Rnd() * rng.Count + 1) Set rng1 = rng(l) If rng1.Interior.ColorIndex = xlNone Then rng1.Interior.ColorIndex = 6 i = i + 1 End If Loop End Sub -- Regards, Tom Ogilvy "Another Jennifer" <Another wrote in message ... I am trying to create a function (?) that will allow me to get a specific number of randomly selected cells out of a range. I know this is available, but I can't seem to get it to do the following: BACKGROUND- I have a spreadsheet that has employee names for the column titles, and training module names for each row. The connecting cells indicate the date which these training modules were completed [ex- Joe Shmoe (column title) was training on Beancounting (row title) on 7/7/77 (coordinating cell data).] PROBLEM- I need to randomly select 315 cells out of a range of employees/training modules (for example, range B2-AF247). I know this can be accomplished through the analysis tool, but I can't seem to get it to simply HIGHLIGHT THE RANDOMLY SELECTED CELLS within the actual spreadsheet. Instead, it seems to only pull the actual data out of the cells and throw it somewhere else, which prevents me from having any reference as to the employee and training module. SOLUTION- ??? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT- Random Sample per range of data
Sub ABCD()
Dim rng As Range Dim rng1 As Range Dim num As Long Dim l As Long, i As Long num = 315 Set rng = Range("B2:AF247") rng.Interior.ColorIndex = xlNone i = 0 Do While i < num l = Int(Rnd() * rng.Count + 1) Set rng1 = rng(l) If rng1.Interior.ColorIndex = xlNone Then rng1.Interior.ColorIndex = 6 cells(i+1,"AH").Value = rng1.Address(0,0) i = i + 1 End If Loop End Sub Should list the cells in column AH -- Regards, Tom Ogilvy "Another Jennifer" wrote in message ... Tom, I've learned how to apply the process, and it works fantastically, thank you!!! I am now wondering if there's a way to list those selected boxes (NOT the data contained in the boxes, but the actual cell #, such as A:220) in a separate column on a separate spreadsheet. "Tom Ogilvy" wrote: Sub ABCD() Dim rng As Range Dim rng1 As Range Dim num As Long Dim l As Long, i As Long num = 315 Set rng = Range("B2:AF247") rng.Interior.ColorIndex = xlNone i = 0 Do While i < num l = Int(Rnd() * rng.Count + 1) Set rng1 = rng(l) If rng1.Interior.ColorIndex = xlNone Then rng1.Interior.ColorIndex = 6 i = i + 1 End If Loop End Sub -- Regards, Tom Ogilvy "Another Jennifer" <Another wrote in message ... I am trying to create a function (?) that will allow me to get a specific number of randomly selected cells out of a range. I know this is available, but I can't seem to get it to do the following: BACKGROUND- I have a spreadsheet that has employee names for the column titles, and training module names for each row. The connecting cells indicate the date which these training modules were completed [ex- Joe Shmoe (column title) was training on Beancounting (row title) on 7/7/77 (coordinating cell data).] PROBLEM- I need to randomly select 315 cells out of a range of employees/training modules (for example, range B2-AF247). I know this can be accomplished through the analysis tool, but I can't seem to get it to simply HIGHLIGHT THE RANDOMLY SELECTED CELLS within the actual spreadsheet. Instead, it seems to only pull the actual data out of the cells and throw it somewhere else, which prevents me from having any reference as to the employee and training module. SOLUTION- ??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I random sample from a set until every sample is selected? | Excel Discussion (Misc queries) | |||
Random Sample with multiple data points | Excel Worksheet Functions | |||
can i do a random sample with non-numeric data? | Excel Discussion (Misc queries) | |||
Selecting a Random Sample of 15 from a Large data set | Excel Worksheet Functions | |||
Is it possible to do a random sample of non-numeric data in Excel? | Excel Discussion (Misc queries) |