How to choose Random rows??

Saarang84

New Member
Joined
Jan 4, 2009
Messages
38
Hi Wizards,

Can you help me with a small requirement in a module in my macro in which i'm trying to automate a random selection of rows?

Here's the background..

I've a workbook with 2 sheets, one called CONFIG and another called MASTER. The CONFIG sheet contains a list of names of users and specifies their type alongside.
In the MASTER sheet, there are few columns including a column of names and i've included a random number column using the formula as below:

=INT(RAND()*1000)+1

1000 indicates that there are 1000 rows in the MASTER sheet.

Here's a piece of code i've developed:


Code:
Sub RandomFilter()
Dim i, x, z, F_Row, L_Row, NameCnt As Integer
Dim vCol, fCol, LR, vCells, UsrType As Long
Dim rngFilter, rngVisible, FilterRange As Range
Dim strUserNames As Variant, Result As Variant
Dim wsM  As Worksheet
Application.ScreenUpdating = False
Set wsM = Sheets("MASTER")
With wsM
  vCol = .Rows(1).Find("LAST_UPDATE_NAME", LookIn:=xlValues, LookAt:=xlWhole).Column
  fCol = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
  LR = .Cells(.Rows.Count, vCol).End(xlUp).Row
  NameCnt = Sheets("CONFIG").Range("A65536").End(xlUp).Row
  ReDim strUserNames(2 To NameCnt) As Variant
  For i = 2 To NameCnt
      strUserNames(i) = Sheets("CONFIG").Cells(i, 1).Value
  Next i
  ReDim Preserve strUserNames(2 To NameCnt) As Variant
  .Cells(1, fCol) = "Flag"
  .Range(.Cells(2, fCol), .Cells(LR, fCol)).FormulaR1C1 = "=INT(RAND()*" & LR & ")+1"
  ' Condition is if UsrType is New, then z = 5%, else if UsrType is Existing, then z = 2.5%
  For i = 1 To NameCnt
    .Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & strUserNames(i)
    .Columns("A:J").AutoFilter field:=9, Criteria1:="<" & (z / 100)
    vCells = .Cells(.Rows.Count, fCol).End(xlUp).Row
    With Range("A1:J" & Cells(Rows.Count, fCol).End(xlUp).Row)
         .Resize(.Rows.Count - 1, 1).Offset(i, fCol).SpecialCells(xlCellTypeVisible) = "Sample_" & i
    End With
    .Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter
  Next
End With
End Sub


My requirement is to randomly choose x% of rows out of the total rows Cleared & Closed by each user. Based on the user type, the value of x varies.

The number of sample rows to be chosen is calculated and rounded-off to the next highest integer using CEILING if decimal value exceeds 0.5

How can random rows be chosen? Can somebody help me?

Sarang
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Why do you need to select the rows?
What do you want to do with the data?
What does cleared and closed by each user mean?
Can you post a sample of your sheet and expected results?
 
Upvote 0
Hi,

Can you tell me to post a sample workbook?? I've one and i'm not able to attach it..

My answers to your questions below

Why do you need to select the rows? -- The data is actually processed by a set of users in a system which needs to be checked for quality / validated. I need to do a random selection on these records for each user

What do you want to do with the data? -- Just by random selection, i want to pick a certain % of records processed by each user based on the category in which he's classified (category is determined by user vintage) - there are 2 categories -- New and Existing.

What does cleared and closed by each user mean? - It means the records processed by each user


Sarang
 
Upvote 0
Hi,

Was able to make some progress with the code (though i'm unable to attach the workbook) need some small tweaking to be done..

I'm able to calculate the number of rows that i should choose as samples. I store this in the variable x (which is calculated inside a if-else block inside the for loop).
My question is, how can i apply filter to a column of random numbers calculated using =RAND() function (in any possible way) to select x samples randomly?

Code:

Code:
Function GetFilteredRowCount(FilterRange As Range) As Long
 
  Dim I As Long
  Dim RowCount As Long
 
    On Error GoTo ReturnRows
 
      With FilterRange.SpecialCells(xlCellTypeVisible).Areas
        For I = 1 To .Count
          RowCount = RowCount + .Item(I).Rows.Count
        Next I
      End With
 
ReturnRows:
    GetFilteredRowCount = RowCount
 
End Function
 
 
Sub Random_Filter()
Dim i As Integer
Dim vCol, fCol, LR, vCells As Long
Dim wsM  As Worksheet
Application.ScreenUpdating = False
Set wsM = Sheets("TestRandom")
With wsM
  vCol = .Rows(1).Find("DATA1", LookIn:=xlValues, LookAt:=xlWhole).Column
  fCol = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
  LR = .Cells(.Rows.Count, vCol).End(xlUp).Row
  .Cells(1, fCol) = "Flag"
  .Range(.Cells(2, fCol), .Cells(LR, fCol)).FormulaR1C1 = "=RAND()"
  ' Check and include Unique Random module here
  '.Range(.Cells(2, fCol), .Cells(LR, fCol)).FormulaR1C1 = "=Unq_Random_Between()"
  For i = 1 To 10
    .Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & i
    .Columns("A:J").AutoFilter field:=9, Criteria1:="<" & (5 / 100)
 
    vCells = .Cells(.Rows.Count, fCol).End(xlUp).Row
    With Range("A1:J" & Cells(Rows.Count, fCol).End(xlUp).Row)
         .Resize(.Rows.Count - 1, 1).Offset(i, fCol).SpecialCells(xlCellTypeVisible) = "Sample_" & i
    End With
    .Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter
  Next
End With
End Sub


The above code varies from the original code i posted initially as this is a test code which i can incorporate into the main module.

Sarang
 
Last edited:
Upvote 0
I've made a cross post for uploading a workbook with test data here.


Sarang
 
Last edited by a moderator:
Upvote 0
Thanks to Zack..

I found the below link by Tushar Mehta on Random Selection
Here's my code where i'm trying to incorporate some code from the above link.

Code:
Sub RandomFilter()
Dim p As String
Dim wsM As Worksheet
Dim rndRowRng(), rndRowSamples() As Variant
Dim rngVSR, rngFR As Range
Dim i, x, rowNum As Integer
Dim vCol, fCol, LR, vCellCount, randRow, A As Long
Application.ScreenUpdating = False
Set wsM = Sheets("TestRandom")
With wsM
vCol = .Rows(1).Find("DATA1", LookIn:=xlValues, LookAt:=xlWhole).Column
fCol = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
LR = .Cells(.Rows.Count, vCol).End(xlUp).Row
.Cells(1, fCol) = "Flag"
.Range(.Cells(2, fCol), .Cells(LR, fCol)).FormulaR1C1 = "=ROW()-1"
 
ReDim rndRowRng(2 To LR) As Variant
ReDim rndRowSamples(2 To LR) As Variant
For i = 1 To 10
.Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & i
If .FilterMode Then
With .AutoFilter.Range
   Set rngVSR = .Columns(1).Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
End With
Else
   MsgBox "Filters not Set." & vbLf & "Processing Terminated."
   Exit Sub
End If
vCellCount = rngVSR.Cells.Count
p = InputBox("Enter Sheet Name: ", "User Type")
Randomize
If p = "New" Then
  randRow = Int(vCellCount * (5 / 100))
ElseIf p = "Existing" Then
  randRow = Int(vCellCount * (2.5 / 100))
End If
For rowNum = 2 To LR
   rndRowRng(i) = rngVSR.Cells(i, 9).Value
Next rowNum
ReDim Preserve rndRowRng(2 To LR) As Variant
 
' Invoke Tushar Mehta's code here
 
For x = LBound(rndRowSamples) To UBound(rndRowSamples)
  With Range("A1:J" & Cells(Rows.Count, fCol).End(xlUp).Row)
     .Resize(.Rows.Count - 1, 1).Offset(i, .Cells(x, 10)).SpecialCells(xlCellTypeVisible) = "Sample_" & i
  End With
Next x
Erase rndRowRng
.Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter
Next i
End With
End Sub

Using .FormulaR1C1 = "=ROW()-1" shows row numbers in a column.
After applying filter to DATA1 column in the sheet, i want to read the visible row numbers and store it in an array.

I'm trying to include the RandomSelect method below (from the above link) to select random row numbers by mixing the (above mentioned) array and mark the selected array elements. The randRow variable contains the number of rows to be chosen from the array.

Return a specified number of random values from an user specified array

Code:
Sub Swap(ByRef Arr() As Variant, ByVal i As Long, ByVal j As Long)
   Dim temp As Variant
   temp = Arr(i): Arr(i) = Arr(j): Arr(j) = temp
End Sub
 
Sub RandomSelect(ByRef Arr() As Variant, ByVal N As Long)
  Dim z As Long, Idx As Long
  'Need edits to ensure Arr is an acceptable data type. Similarly, validate n
  For z = 1 To N
  Idx = LBound(Arr) + (z - 1) + Int((UBound(Arr) - (LBound(Arr) + (z - 1)) + 1) * Rnd())
  Swap Arr, LBound(Arr) + (z - 1), Idx
  Next z
End Sub

I'm not able to incorporate and invoke the above module. Can someone help me incorporating it??


Sarang
 
Upvote 0
Can advancedfilter with xlfiltercopy can be used for the above requirement? If yes, how can that be done? Can someone explain clearly and give some code sample??


Sarang
 
Upvote 0
Hi,

Cross-post links for this thread is as below:

How to choose Random rows?? - The CodeCage Forum

Random Selection of Row Numbers from a Filtered Range - Excel Forum

How to select Random Rows after Autofilter?? - XtremeVBTalk Forum

How to select Random Rows after Autofilter?? - VBA Express 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 is understood in any other way.


Sarang
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,215,839
Messages
6,127,196
Members
449,368
Latest member
JayHo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top