# 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?

• Probably much easier in any programming language, e.g. pick Python. Plausible algorithm; 1. pick a random item in your list, 2. place that item first in the list, 3. pick a random item from the remainder of the list, 4. place that item first in the list, 5. repat, when you have only one item left to randomize, print the list. 6. start over for a new instance. Sep 13, 2015 at 19:54

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.

• To make it more reusable in any place of a spreadsheet, I'd recommend "COLUMN()-COLUMN(\$A\$3)+1" instead of COLUMN(). Do you mind editing the answer yourself / would you accept my edit implementing it? Jan 21, 2016 at 15:50
• Otherwise, very elegant solution, and it works even for list where some elements repeat, i.e. are non-unique! Jan 21, 2016 at 15:50

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

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: