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))

enter image description here

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))

enter image description here

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


Comments

Post a Comment

Popular posts from this blog

Converting A String To Int In Groovy

Answer : Use the toInteger() method to convert a String to an Integer , e.g. int value = "99".toInteger() An alternative, which avoids using a deprecated method (see below) is int value = "66" as Integer If you need to check whether the String can be converted before performing the conversion, use String number = "66" if (number.isInteger()) { int value = number as Integer } Deprecation Update In recent versions of Groovy one of the toInteger() methods has been deprecated. The following is taken from org.codehaus.groovy.runtime.StringGroovyMethods in Groovy 2.4.4 /** * Parse a CharSequence into an Integer * * @param self a CharSequence * @return an Integer * @since 1.8.2 */ public static Integer toInteger(CharSequence self) { return Integer.valueOf(self.toString().trim()); } /** * @deprecated Use the CharSequence version * @see #toInteger(CharSequence) */ @Deprecated public static Integer toInteger(String self) { return toInteg
Read more

"Cannot Create Cache Directory /home//.composer/cache/repo/https---packagist.org/, Or Directory Is Not Writable. Proceeding Without Cache"

Answer : if anyone pass through here, this is shorter solution: sudo chown -R $USER $HOME/.composer it seems to me the group information is missing in your command sudo chown -R <user> /home/<user>/.composer/cache/repo/https---packagist.org Shoud be sudo chown -R <user>:<group> /home/<user>/.composer/cache/repo/https---packagist.org But to avoid other permission issues, I would rather advise: sudo chown -R <user>:<group> /home/<user>/.composer/cache (you'll need access to other folders in there) and sudo chown <user>:<group> /home/<user>/.composer To make sure your user has permissions enough on the global composer folder. Mind the missing recursion so the user don't own keys created by root. If you need to find out the group: groups <user>
Read more

Android How Can I Convert A String To A Editable

Answer : As you probably found out, Editable is an interface so you cannot call new Editable() . However an Editable is nothing more than a String with Spannables, so use SpannableStringBuilder: Editable editable = new SpannableStringBuilder("Pass a string here"); If you only want to change the Spannables, never the text itself, you can use a basic SpannableString. Use Editable.Factory.getInstance().newEditable(str) From the android documentation: Returns a new SpannedStringBuilder from the specified CharSequence. You can override this to provide a different kind of Spanned.
Read more