How to select Random Rows after Autofilter??

Hi Wizards,

This is a cross post from the MrExcel Message Board.
Thread Link: How to choose Random rows??

Its working as i intend it to, but i need to fix it a little bit. Refer the code in attached workbook.

The below line of code needs to be tweaked:

[vba]
.Resize(.Rows.Count - 1, 1).Offset(i, fCol).SpecialCells(xlCellTypeVisible) = "Sample_" & i
[/vba] I want to mark only certain number of rows (stored in the variable randRow) as samples out of the total number of visible rows. But the above line of code marks all the rows filtered. How can this be fixed to randomly mark only the number of rows stored in randRow??

The x number rows which have to be marked is calculated each time the filter changes on the below lines of code:

[vba]
For i = 1 To 10
.Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & i
[/vba]
In general, out of the total number of rows filtered, say y, i need to choose x rows randomly (where x<y).

This is a test data in which i use a for loop (from 1 to 10) and filter each time for 1,2 ...10. Actually, my original data contains a list of names in an array (called names(i), and i=1 to n, for n names in general) and i need to loop for all the n names.


Sarang

Hi,

Cross-post links for this thread is as below:

How to choose Random rows?? - The CodeCage Forum
How to choose Random rows?? - MrExcel Message Board
Random Selection of Row Numbers from a Filtered Range - Excel Forum
How to select Random Rows after Autofilter?? - XtremeVBTalk Forum

I REALLY DO NOT mean to abuse any of the forums where help is on offer, as well as not solely for the purpose of uploading attachments. My only intention was to make it viewed by more people so that i could get help instantly. My sincere apologies if this has been understood in any other way.

Sarang

Sarang, as it may not have been your intention to abuse any board, that is what you have done. And in the words of my father, you're responsible for your own actions. And the other saying he used more often than not was, good intentions are just that - intentions. I do, however, appreciate the token of your apology. I understand what your intention was, regardless of what it turned out to be.

Make sure to read this: http://www.excelguru.ca/node/7 Ken Puls did an excellent job of capturing the information regarding cross-posting on one web page. Please read it. I guarantee you that people will not answer your question when they see it on multiple boards. And as I told you at MrExcel.com, if I see a cross-post I'll just look the other way (in regards to helping).

This thread will stay open as a testament to how little help you will actually receive. Most people will take your links of cross-posts as little more than jewelry on a pig (I don't think my father said that, but somebody I once knew did, loved it). So don't take the ignoring personally. If you'd tried to deceive us, you probably would've been banned. So as far as I'm concerned, all is well, lesson learned, good luck with future posts. You have others helping you on other boards for this question as well. Best of luck.

Hi,

Thanks for the advice... Will never do it again..

Came across a KB entry in this forum by matthewspatrick titled Function to return random sample, with or without replacement which seems to fulfill my requirement.. However, in my case since i use autofiler, i may need to use SpecialCells(xlCellTypeVisible) and my ranges will be broken. The function uses specified ranges.

Will try to integrate this code into mine and will post back.

Any suggestions are welcome as they might help me out in integrating the code with this KB entry.

Sarang

You might think about looping through your range and setting matching rows of your random selection to an array and re-populate that way.

Hi Zack,

I don't fully understanding what you mean, can you explain it in simple terms (in detail) with some algorithm (steps) or sample code (if possible) how to go about achieving it?


Sarang

Hi,

I tried implementing the below algorithm for sampling out rows of data randomly. I understood the workflow of the knowledge base code and tried to use it but to no avail. Need some help with accessing the range part correctly and passing it to the Sample function. Since i apply filter through the code, i need to determine the filtering range, manipulate it correctly and then invoke it. The function coded by patrick mathews fulfills my requirement exactly.

My sampling algorithm is as below:

1. As i need x rows marked randomly from each time the filter changes on the below lines of code (refer code in the workbook attached in the first post)

[vba]For i = 1 To 10
.Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & i[/vba]

I intended to use an array and to read and input the row numbers into it (instead of using random values generated by RAND() function)

In other words, instead of the below line:

[vba].Range(.Cells(2, fCol), .Cells(LR, fCol)).FormulaR1C1 = "=RAND()"[/vba]

I thought of changing it as below to populate row numbers at the end of each row of data:

[vba].Range(.Cells(2, fCol), .Cells(LR, fCol)).FormulaR1C1 = "=ROW()-1"[/vba]

This would generate and populate the row number against each row [instead of RAND()] under the column titled Flag. Then this column should be value-pasted.

2. Now, search for this Flag column and read these row numbers for all visible rows (in filter) during each iteration of the for loop after applying filter and store these row numbers in the array.

3. I know little bit terms with array coding and Redim may need to be used inbetween somewhere. Randomize and choose the row numbers from the array and choose the required number of rows to be marked as samples (the required number of rows to be marked as samples is calculated and stored in the variable randRow). I found Tushar's code which may be used or the KB code in the above post i made.

4. Mark the randomly chosen rows as samples using the below line code (needs to be tweaked a bit):

[vba].Resize(.Rows.Count - 1, 1).Offset(i, fCol).SpecialCells(xlCellTypeVisible) = "Sample_" & i[/vba]

5. After marking the sample rows chosen, flush the contents of the array before starting the next iteration where the filter is changed.

6. Proceed with next iteration of the for loop. Process steps 1 to 5 again.

The only thing i'm concerned about implementing the above steps is to use memory efficiently since arrays are used. I've limited knowledge for coding with arrays, can anybody who can play around coding with arrays help me out to implement this algorithm??


Sarang

Quote:

---

Originally Posted by rangudu_2008

Hi,
........randRow). I found Tushar's code which may be used or the KB code in the above post i made.

4........help me out to implement this algorithm??
Sarang

---

That should read http://www.thecodecage.com/forumz/members/broro183.html pointed you to Tushars code here http://www.thecodecage.com/forumz/ex...tml#post740699 , one other thing you need to learn about crossposting is you should give credit where credit is due.

Do yourself a very big favour and don't alienate any more people!

Quote:

---

That should read http://www.thecodecage.com/forumz/members/broro183.html pointed you to Tushars code here http://www.thecodecage.com/forumz/ex...tml#post740699 , one other thing you need to learn about crossposting is you should give credit where credit is due.

Do yourself a very big favour and don't alienate any more people!

---

Apologies Simon,

I did not mean to steal Rob's credit (in fact i owe him a lot), infact, i've come across Tushar's code earlier before while browsing across forums.
No offences. Sorry again. :bow:


Sarang

Wizards,

I need some help on extracting the specific range of data from an excel sheet (attached) to pass on to the function to get randomly sampled data using patrick mathews code.

I tried developing a module (code below) but instead of transferring contents to a new sheet, i wanted to shuffle rows, mix and reorder them in the source sheet itself. I landed up in a bit of mess most of the time shuffling the contents within the sheet, that's why i tried transferring contents to the new sheet.

Here's the criteria on which i want to extract data for sampling:

There a column called LAST_UPDATE_NAME where a list of names exist.

To get data in a continuous range for sampling, I may need to apply filters to drill down to a final set of data. This final set required should be in a continuous range. Filtering can be done upto 3 or 4 levels.

Initially, need to sort the names column and then by filtering by each name and to get a continuous range of data, i drill down based on various other columns such as KYL_ML_SCORE, ORG_UNIT_NAME, NOTES, RISK WEIGHTING, etc.

I want to get the filtered output (data) in a continous range. At any given time, after filtering by the names column, i may drill down to the final set of data by applying filter to one or more of the above four columns.

Here's the code which i tried out:

[vba]
Sub Filter_N_Transfer()
Dim ACell As Range
Dim WSNew As Worksheet
Dim Rng As Range
Dim ActiveCellInTable As Boolean
Dim nameX As String
Dim f As Filter
Dim w As Worksheet
Const ns As String = "Not Set"
rw = 1
For Each f In ActiveSheet.AutoFilter.Filters
If f.On Then
c1 = Right(f.Criteria1, Len(f.Criteria1) - 1)
If f.Operator Then
op = f.Operator
c2 = Right(f.Criteria2, Len(f.Criteria2) - 1)
Else
op = ns
c2 = ns
End If
Else
c1 = ns
op = ns
c2 = ns
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set ACell = ActiveCell
'nameX = Application.InputBox(Prompt:="Name of the Sheet")
'If nameX = " " Then Exit Sub
ACell.Select
' Copy the Visible data into a new worksheet.
If ActiveCellInTable = False Then
On Error Resume Next
ACell.Parent.AutoFilter.Range.Copy
If Err.Number > 0 Then
MsgBox "Select a Cell in your Data Range"
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Exit Sub
End If
Else
ACell.ListObject.Range.SpecialCells(xlCellTypeVisible).Copy
End If
' Add a new worksheet to copy the Filtered Results
Set WSNew = Worksheets.Add
' Add InputBox to get destination Sheet Name
WSNew.Name = c1 + c2
With WSNew.Range("A1")
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
' Close AutoFilter.
ACell.AutoFilter
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Next
End Sub
[/vba]


Can someone help me fix up the above code??

Sarang

All that is required to be done is to transfer the data to a continuous range by sorting / filtering. How can that be done??

One of my friends suggested the following:

1. Sort by the random column from top to bottom. See how many rows are in the entire column and take x% of that number.
2. Use conditonal formatting in the unsorted Random() column. Color / format the top or bottom x%
3. There is most likely a Large(Range, x% or no or rows) formula and Small would work also.
4. How about picking all random numbers that have a .1 in the tenths digit?
5. This seems like it could go on all random digit day.

Example: Put random numbers in an entire column. Conditional Format the numbers to color the bottom x%. Find two people you want on the same team and press Calculate until they are both CF colored. Use this as your "Random" sorting.

If by the above steps, sorting the column of random values will help to get a continuous range of data, then how can that be done? If it doesn't work, then, how can the filtered range be made continuous?

Any suggestion, Bob? You are the the VBAX lord here :bow:


Sarang