VBA: how to randomize listbox items?

[Kimberley]

Hi,

I'm looking for a way to randomize listbox items after pressing a button. The listbox has 3 columns : 1/ entries number 2/ male name 3/ female name. Columns 2 and 3 always come together, so when one item moves up or down, the other is moving along.

Example:

01   Todd	Britney
02   Jimi	Meredith
03   Serge	Amy
04   Richard	Kim
05   Roger	Gabrielle
06   Mike	Tammy
07   Bill	Jill
08   Jim	Joyce
09   Phil	Judy
10   Bebel	Jules
11   Fredrik	Helen
12   James	Yolanda

After pressing the Randomize button we should get something like this:

01   Serge	Amy
02   Jim	Joyce
03   Fredrik	Helen
04   Mike	Tammy
05   Jimi	Meredith
06   Bill	Jill
07   Roger	Gabrielle
08   Phil	Judy
09   Richard	Kim
10   James	Yolanda
11   Todd	Britney
12   Bebel	Jules

Todd and Britney randomly moved from the 1st position to the 11th but they're still together. Same thing for the other couples.

The xlsm can be downloaded there:

UserForm.xlsm - 0.03MB

Thanks in advance!

 

[ZVI]

Hi, try this:

' Code for "Randomize" button
Private Sub CommandButton1_Click()
  With ListBox1
    .List = RandomSort(.List, 0, 1)
  End With
End Sub

' ZVI:2011-11-29 http://www.mrexcel.com/forum/showthread.php?t=595216
' Random Sorting of List2D array
' Optional [EnumCol] defines the enumeration column number
' Optional [EnumAdder = 1] is useful for enumeration of list items starting from #1
Function RandomSort(List2D, Optional EnumCol, Optional EnumAdder = 1)
  
  Dim a(), b&(), i&, j&, lb1&, lb2&, ub1&, ub2&, v#
  
  lb1 = LBound(List2D, 1)
  ub1 = UBound(List2D, 1)
  lb2 = LBound(List2D, 2)
  ub2 = UBound(List2D, 2)
  ReDim a(lb1 To ub1)
  ReDim b(lb1 To ub1)
  
  ' Populate a() with random double numbers with less than zero values
  Randomize
  For i = lb1 To ub1
    a(i) = Rnd - 1
  Next
  
  ' Rank a() and put it (that is, random row order) in b()
  For i = lb1 To ub1
    v = 0
    For j = lb1 To ub1
      If a(j) < v Then
        v = a(j)
        b(i) = j
      End If
    Next
    a(b(i)) = 0
  Next
  
  ' Create new output array a()
  ReDim a(lb1 To ub1, lb2 To ub2)
  
  ' Copy from List2D to a() in order of b() values
  For i = lb1 To ub1
    For j = lb2 To ub2
      a(i, j) = List2D(b(i), j)
    Next
    If Not IsMissing(EnumCol) Then a(i, EnumCol) = i + EnumAdder
  Next
  
  ' Return a()
  RandomSort = a()
  
End Function

 

[Kimberley]

Thanks Vladimir, it's working perfectly! And that's fortunate because I can't even read/understand the RandomSort function.

 

[ZVI]

My pleasure, Kimberley - it’s good if it works for you!

Comparing with formula solution may shed the light on algorithm.

The code of the 1st commented part populate a() array by random numbers like the formulas in D-column in the example shown below.

The code of the 2nd commented part provides ranking of random numbers with the similar result in b() array as in returned result of the single array formula of D2:D20 range.

For formula solution range B2:E20 should be sorted by E-column.
But for VBA we do the same by copying of initial list into new empty array a() row by row in the order of b() array values.
That new a() array returns as the result of the function.
Simple enough, isn’t it?

Excel Workbook
	A	B	C	D	E
1	EnumCol	Males	Females	Populate a() by Rnd	Rank a() and put it in b()
2	1	Todd	Britney	0.633498187	8
3	2	Jimi	Meredith	0.267498705	16
4	3	Serge	Amy	0.863522272	3
5	4	Richard	Kim	0.418182435	12
6	5	Roger	Gabrielle	0.99505131	1
7	6	Mike	Tammy	0.694402632	7
8	7	Bill	Jill	0.732774208	5
9	8	Jim	Joyce	0.989122082	2
10	9	Phil	Judy	0.517306323	10
11	10	Bebel	Jules	0.841033514	4
12	11	Fredrik	Helen	0.058784649	19
13	12	James	Yolanda	0.299618608	15
14	13	Kirk	Tania	0.148951264	17
15	14	Robert	Cynthia	0.064311478	18
16	15	Lars	Liv	0.492047581	11
17	16	Fred	Beth	0.549445985	9
18	17	Wes	Liz	0.363060131	14
19	18	John	Bibi	0.700691669	6
20	19	Sam	Lauren	0.374743892	13
Sheet

Regards

 

[Kimberley]

Thanks Vladimir for the explanations. Seems quite easier with excel formulas