Can I Produce A Row In Excel Which Is Random Permutation Of Another Row?

Answer :

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.

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