Excel. Randomize a list with conditions.

[fneily]

In Mr. John Walkenbach's book "Excel 2002 Formulas, in chapter 25, he has the code to Randomize a Range. Code works great. However, how do you change the code to include the following conditions:
The resulting list when placed next to the original will NOT have matches?
And when randomized again, the immediate previous combinations will not be duplicated?
example
Orginal New Randomized
John Bob
Bob Jack
Mary John
Jack Mary

The next time the second column is randomized, new names appear for each original one.

If anyone has the time to code this, many thanks in advance.
Neil

 

[xlhelp]

Not sure if there is enough grey matter to handle this, but would you mind posting the code or function you are using?

 

[fneily]

Here is the Function from the book. rng is the input range, eg. (a2:a10)
So you would select b2:b10, then in b2, you'd type =rangerandomize(a2:a10) then hit CTRL+SHIFT+ENTER to make an array function.
The code is pretty straight forward. I'm just a pretty bad programmer.

Function RANGERANDOMIZE(rng)

Application.Volatile True

Dim V() As Variant, ValArray() As Variant
Dim CellCount As Double
Dim i As Integer, j As Integer
Dim r As Integer, c As Integer
Dim Temp1 As Variant, Temp2 As Variant
Dim RCount As Integer, CCount As Integer

Randomize

' Return an error if rng is too large
CellCount = rng.Count
If CellCount > 1000 Then
RANGERANDOMIZE = CVErr(xlErrNA)
Exit Function
End If

' Assign variables
RCount = rng.Rows.Count
CCount = rng.Columns.Count
ReDim V(1 To RCount, 1 To CCount)
ReDim ValArray(1 To 2, 1 To CellCount)

' Fill ValArray with random numbers
' and values from rng
For i = 1 To CellCount
ValArray(1, i) = Rnd
ValArray(2, i) = rng(i)
Next i

' Sort ValArray by the random number dimension
For i = 1 To CellCount
For j = i + 1 To CellCount
If ValArray(1, i) > ValArray(1, j) Then
Temp1 = ValArray(1, j)
Temp2 = ValArray(2, j)
ValArray(1, j) = ValArray(1, i)
ValArray(2, j) = ValArray(2, i)
ValArray(1, i) = Temp1
ValArray(2, i) = Temp2
End If
Next j
Next i

' Put the randomized values into the V array
i = 0
For r = 1 To RCount
For c = 1 To CCount
i = i + 1
V(r, c) = ValArray(2, i)
Next c
Next r
RANGERANDOMIZE = V
End Function

 

[xlhelp]

Getting old, can't remember anymore. But, I believe that if you seed the random function than that will generate a new random sequence everytime. Especially, if you use the system clock to seed the function, even better.

so, your rnd would become rnd(now())

That may work.

 

[fneily]

I didn't make my question clear. In my example, the first column is the orginal data. I then randomized it to get the second column. In the code, I place Application.Volatile True so that when a cell "off to the side" is changed, the function generates a new randomized list. The frist problem is, the randomization may place Mary next to Mary, for example. No original data from the first column should match the corresponding randomized result.
Also, as in my example, Mary is first matched with John. The next randomization should not have John next to Mary.

Neil

 

[xlhelp]

For this, if I am not mistaken, you would need to build an array that keeps track of which combination has been randomized. You need to depend on someone that does coding on a regular basis.

 

[fneily]

If anyone also sees this question, I've already written the code to solve my problem. Thanks anyway.

Neil