Can I produce a row in Excel which is random permutation of another row?

I want to generate a series of permutations of a row in Excel.

The genesis row could be, for example,

cat   | dog   | rat   | mouse | rhino | ape   | fish

I would like to generate an arbitrary number of other rows with the same contents but shuffled, for example

dog   | mouse | rhino | ape   | cat   | fish  | rat
rhino | rat   | cat   | mouse | fish  | ape   | dog
...

Is this possible?

Solution 1:

Place the values in A1 through G1

In A2 through G2 enter:

=RAND()

In A3 through G3 enter:

=INDEX($A$1:$G$1,MATCH(LARGE($A$2:$G$2,COLUMN()),$A$2:$G$2,0))

Each time the worksheet is re-calculated, a new permutation will be generated.

Solution 2:

I use a method similar to what Gary's Student posted, but I use RANK in my formula instead. I think this simplifies the formula and makes it a little easier to understand.

For sample data in A1:G1:

dog    mouse    rhino    ape    cat    fish    rat

Fill the formula =RAND() across A2:G2.

Then fill the formula below across A3:G3.

=INDEX($A$1:$G$1,RANK(A2,$A2:$G2))

This is good for a one-off or a small number of rows.

For a more robust solution, I would use VBA. The macro below will allow you to select the values you want to shuffle and specify the number of permutations you'd like to create. The permutations will be printed to a new sheet, where you can copy and paste them wherever you like.

Sub nPerm()
Dim ValuesToPermute As Range, arrIn() As Variant, arrTmp() As Variant
Dim pcount As Long
Dim arrOut() As Variant, shtOut As Worksheet

'Get values to permute from user input
Set ValuesToPermute = Application.InputBox("Select values to permute. (Input must be in a single row.)", Type:=8)

'Get number of permutations wanted from user input
pcount = Application.InputBox("How many permutations would you like?", Type:=1)

'Set up array to hold input
arrIn = ValuesToPermute.Value

'Set up array to hold output
ReDim arrOut(1 To pcount, 1 To UBound(arrIn, 2)) As Variant

'Populate output array with n randomly permuted sets
For i = 1 To pcount
    arrTmp = ShuffleArray(arrIn)
    For k = 1 To UBound(arrTmp, 2)
        arrOut(i, k) = arrTmp(1, k)
    Next k
Next i

'Create new sheet and print output there
Set shtOut = Worksheets.Add
shtOut.Name = "nPerm Output"
shtOut.Range("a1").Resize(UBound(arrOut, 1), UBound(arrOut, 2)).Value = arrOut
End Sub


'Modified code from Chip Pearson
'Source: www.cpearson.com/excel/ShuffleArray.aspx Copyright 2018, Charles H. Pearson
Function ShuffleArray(InArray() As Variant) As Variant()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ShuffleArray
' This function returns the values of InArray in random order. The original
' InArray is not modified.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim N As Long
    Dim Temp As Variant
    Dim J As Long
    Dim Arr() As Variant


    Randomize
    L = UBound(InArray, 2) - LBound(InArray, 2) + 1
    ReDim Arr(1 To 1, LBound(InArray, 2) To UBound(InArray, 2))
    For N = LBound(InArray, 2) To UBound(InArray, 2)
        Arr(1, N) = InArray(1, N)
    Next N
    For N = LBound(InArray, 2) To UBound(InArray, 2)
        J = CLng(((UBound(InArray, 2) - N) * Rnd) + N)
        Temp = Arr(1, N)
        Arr(1, N) = Arr(1, J)
        Arr(1, J) = Temp
    Next N
    ShuffleArray = Arr
End Function

ShuffleArray function is not my work.
Source: www.cpearson.com/excel/ShuffleArray.aspx Copyright 2018, Charles H. Pearson

Solution 3:

It is absolutely possible, but the techniques for setting this up are far from obvious.

For each new row, to generate numbers pointing to positions in the original row, I would rank a series of random numbers by size from largest to smallest, using the RANK() and RAND() functions.

Excel does not prevent RAND() from generating the same random number more than once. To circumvent this issue, I would use modified random numbers that are forced to be different from all of the others in the list. My expressions round each random number and then add a unique small number (which is smaller than the rounding increment) to do this.

(My modification causes some permutations to be trivially more probable than others, and I am assuming that you don't need the document's randomness to be of the very highest caliber, or else you wouldn't be using Excel as your random number generator.)

After making the rankings, I would copy and paste them to new cells as values. Then I would tie each new rearranged row to the cells with the pasted rankings, and to the original row, using the OFFSET() function in my formulas.

As an alternative, you could tie the rearranged rows to the rankings without copying and pasting them as values. Doing that would cause your rows to be shuffled every time Excel recalculates the document, because numbers generated from the RAND() function are not sticky in Excel.

Images are probably essential to understanding what I have described.  Click on the below to see large images:

Formula View:

Regular View: