petkovst

New Member
Joined
Jul 1, 2016
Messages
2
Hello guys,
I have 18 numbers (not from 1 to 18) which I need to randomly combine with each other in groups of 6.
Will appreciate any help on that.

Have a good one!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
Hi. You can achieve this with RAND or RANDBETWEEN on the worksheet but that's a volatile function so it will re-calculate the groups each time you change the value in any other cell. If that's not acceptable then you'll need to use VBA to create the groups. Which do you prefer?

WBD
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
original 18 in column A, random combined in Col D in 3x6

try this

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">365</td><td style="text-align: right;background-color: #FFFF00;;">0.015254</td><td style="text-align: right;background-color: #FFFF00;;">18</td><td style="text-align: right;background-color: #FFFF00;;">500</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">108</td><td style="text-align: right;;">0.465486</td><td style="text-align: right;;">11</td><td style="text-align: right;;">208</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">75</td><td style="text-align: right;;">0.31014</td><td style="text-align: right;;">16</td><td style="text-align: right;;">192</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0.826492</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">265</td><td style="text-align: right;;">0.69175</td><td style="text-align: right;;">6</td><td style="text-align: right;;">422</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">233</td><td style="text-align: right;;">0.632774</td><td style="text-align: right;;">8</td><td style="text-align: right;;">265</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">7</td><td style="text-align: right;;">0.460137</td><td style="text-align: right;;">12</td><td style="text-align: right;;">284</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">192</td><td style="text-align: right;;">0.827033</td><td style="text-align: right;;">3</td><td style="text-align: right;;">233</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">500</td><td style="text-align: right;;">0.908309</td><td style="text-align: right;;">1</td><td style="text-align: right;;">296</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">208</td><td style="text-align: right;;">0.887049</td><td style="text-align: right;;">2</td><td style="text-align: right;;">24</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">296</td><td style="text-align: right;;">0.589543</td><td style="text-align: right;;">9</td><td style="text-align: right;;">108</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">318</td><td style="text-align: right;;">0.34369</td><td style="text-align: right;;">15</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">24</td><td style="text-align: right;;">0.498427</td><td style="text-align: right;;">10</td><td style="text-align: right;;">456</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">66</td><td style="text-align: right;;">0.345126</td><td style="text-align: right;;">14</td><td style="text-align: right;;">66</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">284</td><td style="text-align: right;;">0.66784</td><td style="text-align: right;;">7</td><td style="text-align: right;;">318</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">456</td><td style="text-align: right;;">0.394275</td><td style="text-align: right;;">13</td><td style="text-align: right;;">75</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">422</td><td style="text-align: right;;">0.693502</td><td style="text-align: right;;">5</td><td style="text-align: right;;">140</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">140</td><td style="text-align: right;;">0.07961</td><td style="text-align: right;;">17</td><td style="text-align: right;;">365</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=RAND(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C1</th><td style="text-align:left">=RANK(<font color="Blue">B1,$B$1:$B$18</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D1</th><td style="text-align:left">=INDEX(<font color="Blue">$A$1:$A$18,MATCH(<font color="Red">ROW(<font color="Green">C1</font>),$C$1:$C$18,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

petkovst

New Member
Joined
Jul 1, 2016
Messages
2
Well, I would like to be able to regenerate them periodically (students tests).
 

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">365</td><td style="text-align: right;;">0.224007</td><td style="text-align: right;;">11</td><td style="text-align: right;;">456</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">108</td><td style="text-align: right;;">0.035846</td><td style="text-align: right;;">17</td><td style="text-align: right;;">140</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">75</td><td style="text-align: right;;">0.086352</td><td style="text-align: right;;">16</td><td style="text-align: right;;">66</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0.164264</td><td style="text-align: right;;">14</td><td style="text-align: right;;">208</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">265</td><td style="text-align: right;;">0.179736</td><td style="text-align: right;;">12</td><td style="text-align: right;;">233</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">233</td><td style="text-align: right;;">0.573201</td><td style="text-align: right;;">5</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">7</td><td style="text-align: right;;">0.510665</td><td style="text-align: right;;">6</td><td style="text-align: right;;">318</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">192</td><td style="text-align: right;;">0.032195</td><td style="text-align: right;;">18</td><td style="text-align: right;;">284</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">500</td><td style="text-align: right;;">0.403336</td><td style="text-align: right;;">9</td><td style="text-align: right;;">500</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">208</td><td style="text-align: right;;">0.670221</td><td style="text-align: right;;">4</td><td style="text-align: right;;">422</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">296</td><td style="text-align: right;;">0.166137</td><td style="text-align: right;;">13</td><td style="text-align: right;;">365</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">318</td><td style="text-align: right;;">0.476179</td><td style="text-align: right;;">7</td><td style="text-align: right;;">265</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">24</td><td style="text-align: right;;">0.090182</td><td style="text-align: right;;">15</td><td style="text-align: right;;">296</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">66</td><td style="text-align: right;;">0.803286</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">284</td><td style="text-align: right;;">0.43613</td><td style="text-align: right;;">8</td><td style="text-align: right;;">24</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">456</td><td style="text-align: right;;">0.908631</td><td style="text-align: right;;">1</td><td style="text-align: right;;">75</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">422</td><td style="text-align: right;;">0.31553</td><td style="text-align: right;;">10</td><td style="text-align: right;;">108</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">140</td><td style="text-align: right;;">0.809128</td><td style="text-align: right;;">2</td><td style="text-align: right;;">192</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=MyRand(<font color="Blue">$E$1, ROW(<font color="Red"></font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C1</th><td style="text-align:left">=RANK(<font color="Blue">$B1,$B$1:$B$18</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D1</th><td style="text-align:left">=INDEX(<font color="Blue">$A$1:$A$18,MATCH(<font color="Red">ROW(<font color="Green"></font>),$C$1:$C$18,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Code:
Public Function MyRand(seed As Long, occurrence As Long)

MyRand = Rnd(-seed)
Do While occurrence > 1
    MyRand = Rnd
    occurrence = occurrence - 1
Loop

End Function

Change the value in E1 to generate a new list.

WBD
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,473
Messages
5,596,356
Members
414,061
Latest member
JJSB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top