Durstenfeld’s Algorithm Jumble Array

[YasserKhalil]

Hello everyone

Can you guide me to a tutorial that gives me examples of Durstenfeld’s Algorithm to jumble array ..?
Or even give me an example of how to use that Algorithm.

[HansV]

See for example Random Number within a range without repetition in VBA

[YasserKhalil]

Thanks a lot for the link
In fact, I am trying to get the algorithm with simple example so as to get it well
Say I have the range A1:C3 and there are data which I need to jumble or shuffle. How can I use this algorithm to jumble the array and avoid using the Randomize function?

[HansV]

Any implementation of a shuffle algorithm will need to use a random function!

[YasserKhalil]

I tried something but seems to reverse the values in the range not to jumble them

Code: Select all

Sub Test()
    Dim n As Integer
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim nr As Integer
    Dim nc As Integer
    Dim rn As Integer
    Dim temp
    
    With Range("A1").CurrentRegion
        n = .Cells.Count
        nr = .Rows.Count
        nc = .Columns.Count
    End With
    
    k = 1
    ReDim a(1 To n)
    
    For i = 1 To nr
        For j = 1 To nc
            rn = WorksheetFunction.RandBetween(1, n - k + 1)
            a(rn) = Cells(i, j)
            
            temp = a(n - k + 1)
            a(n - k + 1) = a(rn)
            a(rn) = temp
            k = k + 1
        Next j
    Next i
    
End Sub

Here's a link of PDF file that I rely on but it seems I didn't get it well
https://we.tl/t-SrSfoA2RMQ" onclick="window.open(this.href);return false;

[HansV]

Before you can shuffle the array a, you have to fill it, as the PDF explains. You only start shuffling it after you have filled it.

[YasserKhalil]

The range already has data so how can I fill it? I am confused !!

[HansV]

You have to fill the one-dimensional array a with the values of the range.

[YasserKhalil]

Do you mean I have to use twice loops for the purpose .. to fill the array a first a(rn) = Cells(i, j) ??

[YasserKhalil]

Do you mean that?

Code: Select all

Option Explicit

Sub Test()
    Dim n As Integer
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim nr As Integer
    Dim nc As Integer
    Dim rn As Integer
    Dim Temp
    
    With Range("A1").CurrentRegion
        n = .Cells.Count
        nr = .Rows.Count
        nc = .Columns.Count
    End With
    
    k = 1
    ReDim a(1 To n)
    
    For i = 1 To nr
        For j = 1 To nc
            a(k) = Cells(i, j)
            k = k + 1
        Next j
     Next i
    
    k = 1
    For i = 1 To nr
        For j = 1 To nc
            rn = WorksheetFunction.RandBetween(1, n - k + 1)
            Temp = a(n - k + 1)
            a(n - k + 1) = a(rn)
            a(rn) = Temp
            k = k + 1
        Next j
    Next i
    
End Sub

And how can I restore the new array to the range again?

[HansV]

You have to loop three times:

1) Fill the array a with the values of the range
2) Apply the Durstenfeld algorithm to shuffle the array
3) Fill the range with the items of the array - this is the reverse of 1)

[YasserKhalil]

I am stuck at step 3. Can you please post the code for this step?

[HansV]

It's a homework assignment - you should be able to work it out for yourself.

You have to loop through the rows and columns of the range, and assign the next available array item to the cell.

[YasserKhalil]

In fact, this is not mine. This is for a friend and I am trying to help
And I have posted the thread because I am stuck at doing it.

[HansV]

The loop that fills the array with cell values is

Code: Select all

    k = 1
    For i = 1 To nr
        For j = 1 To nc
            a(k) = Cells(i, j)
            k = k + 1
        Next j
     Next i

You need to change only one line to fill the cell values from the array...

[YasserKhalil]

Thanks a lot. I got it now.

[YasserKhalil]

As for Mr. Hans what the best code for doing that regardless this algorithm. Can you give me example code of how to shuffle the values within any range?

[HansV]

This is a good algorithm - it shuffles the array (range) without bias.

[YasserKhalil]

What bias? I didn't get that word.
can the code be shortened or converted to be used as UDF?

[HansV]

In some shuffling algorithms, not all items have the same probability of being shuffled. This is called bias. The Durstenfeld algorithm treats all values equally.

What would be the purpose of a UDF here?