"Shake" the list after one iteration

[vas54404]

Hi all,

I'm using an excel file which selects 5 names out of a pool of 50 names. The sheet is such that if 5 names are selected randomly, next time 5 names will be chosen from remaining 45 names. This goes on till 1 cycle is complete.

However, in the next cycle which will be iteration 11, I see same names which were in iteration 1 and names in iteration 2 is same as in iteration 12.

Is it possible to "shake" the list after 1 complete cycle, so that iteration 11 is not same as iteration 1 and so on..
Thanks for your help!

 

[ranman256]

what code are you using to 'randomize'?

 

[ranman256]

your 'generator' may not be correct. Use some of this code:

Public Sub Random5()
Dim colNames As New Collection
Const kPICKS = 5
Dim i As Integer, iCnt As Integer, iNum As Integer, iCol As Integer
Dim rStart As Range


On Error GoTo errRand


  'load the collection
Sheets("names").Select
Range("E2").Select
While ActiveCell.Value <> ""
   colNames.Add ActiveCell.Value
   ActiveCell.Offset(1, 0).Select   'next row
Wend


Sheets.Add
Set rStart = Range("A1")
While colNames.Count > 0
    For i = 1 To kPICKS
      iCnt = colNames.Count
      iNum = Int(1 + Rnd() * (iCnt - 1 + 1))
      
      ActiveCell.Value = colNames(iNum)
      colNames.Remove iNum
      
       ActiveCell.Offset(1, 0).Select   'next row
    Next
    
    iCol = iCol + 1
    rStart.Offset(0, iCol).Select
Wend
errRand:
Set rStart = Nothing
End Sub

 

[vas54404]

Thanks for your reply. I'm using this code:

Code:
Sub SortData()
'
' SortData Macro
'


'
ActiveWorkbook.Worksheets("Book1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Book1").Sort.SortFields.Add Key:=Range( _
"AK2:AK43"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Book1").Sort.SortFields.Add Key:=Range( _
"AJ2:AJ43"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Book1").Sort
.SetRange Range("A1:AK43")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


End Sub

 

[Rick Rothstein]

your 'generator' may not be correct. Use some of this code:

Public Sub Random5()
Dim colNames As New Collection
Const kPICKS = 5
Dim i As Integer, iCnt As Integer, iNum As Integer, iCol As Integer
Dim rStart As Range

Randomize

On Error GoTo errRand


  'load the collection
Sheets("names").Select
Range("E2").Select
While ActiveCell.Value <> ""
   colNames.Add ActiveCell.Value
   ActiveCell.Offset(1, 0).Select   'next row
Wend


Sheets.Add
Set rStart = Range("A1")
While colNames.Count > 0
    For i = 1 To kPICKS
      iCnt = colNames.Count
      iNum = Int(1 + Rnd() * (iCnt - 1 + 1))
      
      ActiveCell.Value = colNames(iNum)
      colNames.Remove iNum
      
       ActiveCell.Offset(1, 0).Select   'next row
    Next
    
    iCol = iCol + 1
    rStart.Offset(0, iCol).Select
Wend
errRand:
Set rStart = Nothing
End Sub

You should add the Randomize statement I show in red above otherwise everytime Excel is restarted it will generate the same list.

 

[vas54404]

Thanks for your reply. I'm using following Code: How can I randomize this?

Code:
Sub SortData()
'
' SortData Macro
'


'
ActiveWorkbook.Worksheets("Book1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Book1").Sort.SortFields.Add Key:=Range( _
"AK2:AK43"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Book1").Sort.SortFields.Add Key:=Range( _
"AJ2:AJ43"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Book1").Sort
.SetRange Range("A1:AK43")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


End Sub

You should add the Randomize statement I show in red above otherwise everytime Excel is restarted it will generate the same list.

 

[Rick Rothstein]

Thanks for your reply. I'm using following Code: How can I randomize this?

Code:
Sub SortData()
'
' SortData Macro
'


'
ActiveWorkbook.Worksheets("Book1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Book1").Sort.SortFields.Add Key:=Range( _
"AK2:AK43"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Book1").Sort.SortFields.Add Key:=Range( _
"AJ2:AJ43"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Book1").Sort
.SetRange Range("A1:AK43")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


End Sub

I am guessing you have formulas in cells AK2:AK43 generating random numbers and that you are sorting your data according to that column. You don't need to do anything special for that setup... the Randomize statement that I suggest to ranman256 was because his code used VB's Rnd function and that function needs the Randomize statement to generate different sequences of random numbers each time the workbook is opened for the first time.

 

[vas54404]

I am guessing you have formulas in cells AK2:AK43 generating random numbers and that you are sorting your data according to that column. You don't need to do anything special for that setup... the Randomize statement that I suggest to ranman256 was because his code used VB's Rnd function and that function needs the Randomize statement to generate different sequences of random numbers each time the workbook is opened for the first time.

Thanks for your reply. But my list doesn't get "shaken" up. I always get same 5 names together.

 

[Rick Rothstein]

Thanks for your reply. But my list doesn't get "shaken" up. I always get same 5 names together.

Tell us about the layout of your worksheet (what constants and formulas are in what cells).