Non-Repeating Random Selection of An Array

jworkman7

Board Regular
Joined
Jul 1, 2015
Messages
50
A1 thru A5 has a list of fruits (Apple, Orange, Watermelon, Banana, Pineapple). B1-C5 has a listing of sub-types of each these fruits. I would like an output in E1 thru E7 which will give me a random selection of 7 these subtypes in the B1-C5 array, and the random selection should contain at least one subtype of each fruit type from column A, but can contain more than 1 subtype. Again, it's random, so there might be 2 apple types, 2 banana types, and 1 each of Orange, Watermelon, and Pineapple. I do want to make sure that the output in E1 to E7 does not repeat values.

Any ideas?
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
Do you want all selections satisfying your criteria to be equiprobable?

J.Ty.
 

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
Below is a solution.
Column H already contains a random selection satisfying your criteria (all choices are equiprobable, up to the quality of the Excel's random number generator), but is a very nonrandom order.
The the columns to the right of it are used to sort it a random order (all orders are almost equiprobable). the final result in in column E.

J.Ty.

P.S. Note that, except column G, you can copy the formula from row 1 down. In column G you can make only 5 copies, formulas in rows 6 and 7 are different.

<b>Excel 2010</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 /><col /><col /><col /><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><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Apple</td><td style=";">Apple1</td><td style=";">Apple2</td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;"> Watermelon1</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style=";">Apple2</td><td style="text-align: right;;">18</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";"> Orange</td><td style=";"> Orange1</td><td style=";"> Orange2</td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;"> Pineapple1</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";"> Orange1</td><td style="text-align: right;;">39</td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">6</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";"> Watermelon</td><td style=";"> Watermelon1</td><td style=";"> Watermelon2</td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;"> Banana2</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style=";"> Watermelon2</td><td style="text-align: right;;">32</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";"> Banana</td><td style=";"> Banana1</td><td style=";"> Banana2</td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;">Apple2</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style=";"> Banana2</td><td style="text-align: right;;">17</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";"> Pineapple</td><td style=";"> Pineapple1</td><td style=";"> Pineapple2</td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;"> Watermelon2</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";"> Pineapple1</td><td style="text-align: right;;">15</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;"> Orange1</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style=";"> Watermelon1</td><td style="text-align: right;;">13</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;"> Pineapple2</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style=";"> Pineapple2</td><td style="text-align: right;;">65</td><td style="text-align: right;;">6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">7</td><td style="text-align: right;;">7</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">G1</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">1,2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H1</th><td style="text-align:left">=INDEX(<font color="Blue">B1:C1,G1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I1</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">1,100</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J1</th><td style="text-align:left">=COUNTIF(<font color="Blue">$I$1:$I$7,"<"&I1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K1</th><td style="text-align:left">=COUNTIF(<font color="Blue">$I$1:$I1,"="&I1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">L1</th><td style="text-align:left">=J1+K1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">M1</th><td style="text-align:left">=MATCH(<font color="Blue">ROW(<font color="Red"></font>),$L$1:$L$7,0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G2</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">1,2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H2</th><td style="text-align:left">=INDEX(<font color="Blue">B2:C2,G2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I2</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">1,100</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$I$1:$I$7,"<"&I2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$I$1:$I2,"="&I2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">L2</th><td style="text-align:left">=J2+K2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">M2</th><td style="text-align:left">=MATCH(<font color="Blue">ROW(<font color="Red"></font>),$L$1:$L$7,0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G3</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">1,2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H3</th><td style="text-align:left">=INDEX(<font color="Blue">B3:C3,G3</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I3</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">1,100</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$I$1:$I$7,"<"&I3</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$I$1:$I3,"="&I3</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">L3</th><td style="text-align:left">=J3+K3</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">M3</th><td style="text-align:left">=MATCH(<font color="Blue">ROW(<font color="Red"></font>),$L$1:$L$7,0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G4</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">1,2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H4</th><td style="text-align:left">=INDEX(<font color="Blue">B4:C4,G4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I4</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">1,100</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J4</th><td style="text-align:left">=COUNTIF(<font color="Blue">$I$1:$I$7,"<"&I4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K4</th><td style="text-align:left">=COUNTIF(<font color="Blue">$I$1:$I4,"="&I4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">L4</th><td style="text-align:left">=J4+K4</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">M4</th><td style="text-align:left">=MATCH(<font color="Blue">ROW(<font color="Red"></font>),$L$1:$L$7,0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G5</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">1,2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H5</th><td style="text-align:left">=INDEX(<font color="Blue">B5:C5,G5</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I5</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">1,100</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J5</th><td style="text-align:left">=COUNTIF(<font color="Blue">$I$1:$I$7,"<"&I5</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K5</th><td style="text-align:left">=COUNTIF(<font color="Blue">$I$1:$I5,"="&I5</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">L5</th><td style="text-align:left">=J5+K5</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">M5</th><td style="text-align:left">=MATCH(<font color="Blue">ROW(<font color="Red"></font>),$L$1:$L$7,0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G6</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">1,5</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H6</th><td style="text-align:left">=INDEX(<font color="Blue">B1:C5,G6,IF(<font color="Red">INDEX(<font color="Green">G1:G5,G6</font>)=2,1,2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I6</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">1,100</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J6</th><td style="text-align:left">=COUNTIF(<font color="Blue">$I$1:$I$7,"<"&I6</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K6</th><td style="text-align:left">=COUNTIF(<font color="Blue">$I$1:$I6,"="&I6</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">L6</th><td style="text-align:left">=J6+K6</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">M6</th><td style="text-align:left">=MATCH(<font color="Blue">ROW(<font color="Red"></font>),$L$1:$L$7,0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G7</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">1,4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H7</th><td style="text-align:left">=INDEX(<font color="Blue">B1:C5,IF(<font color="Red">G7>=G6,G7+1,G7</font>),IF(<font color="Red">INDEX(<font color="Green">G1:G5,IF(<font color="Purple">G7>=G6,G7+1,G7</font>)</font>)=2,1,2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I7</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">1,100</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J7</th><td style="text-align:left">=COUNTIF(<font color="Blue">$I$1:$I$7,"<"&I7</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K7</th><td style="text-align:left">=COUNTIF(<font color="Blue">$I$1:$I7,"="&I7</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">L7</th><td style="text-align:left">=J7+K7</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">M7</th><td style="text-align:left">=MATCH(<font color="Blue">ROW(<font color="Red"></font>),$L$1:$L$7,0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E1</th><td style="text-align:left">=INDEX(<font color="Blue">$H$1:$H$7,M1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">=INDEX(<font color="Blue">$H$1:$H$7,M2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E3</th><td style="text-align:left">=INDEX(<font color="Blue">$H$1:$H$7,M3</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E4</th><td style="text-align:left">=INDEX(<font color="Blue">$H$1:$H$7,M4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E5</th><td style="text-align:left">=INDEX(<font color="Blue">$H$1:$H$7,M5</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E6</th><td style="text-align:left">=INDEX(<font color="Blue">$H$1:$H$7,M6</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E7</th><td style="text-align:left">=INDEX(<font color="Blue">$H$1:$H$7,M7</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

jworkman7

Board Regular
Joined
Jul 1, 2015
Messages
50
Hi J.Ty, Thank you very much for this in-depth and innovative response. One question. I am now adapting this to my rather large data set, and have so far amended my ranges, because in fact, each Fruit in my data (I have about 100) needs will have about 75 sub-categories. What should I adjust the Randbetween functions in G2 to be? 75? Also, can I just drag G7 down for as long as I need, and produce the same results. I believe I can because I have been playing with this, but I just wanted to confirm my conclusion with the Master--You! Thanks in advance
 

jworkman7

Board Regular
Joined
Jul 1, 2015
Messages
50
I just noticed that after adjusting some ranges, I am getting some duplicates in column E--2 or 3 of some results. So, I have some more questions. Assuming I have 99 rows:

- I've adjusted E 1 to =INDEX($H$1:$H$99,M1). Is this correct?
- I've dragged G7 all the way down to G99. Is that correct?
- I adjusted H7 to =INDEX(B1:C99,IF(G7>=G6,G7+1,G7),IF(INDEX(G1:G5,IF(G7>=G6,G7+1,G7))=2,1,2)), to account for the 99 rows, then dragged it all the way down to row 99. Is that correct?
- I adjusted J1 to =COUNTIF($I$1:$I$99,"<"&I1). Is this correct?
- I adjusted M1 to =MATCH(ROW(),$L$1:$L$99,0). Is this correct?
 

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
The formuls you need depend very much on the parameters of the choice you need.

Please let me explain how it works on the small example, to give you insight into what you need to consider:

  1. The first 5 formulas in column G generate random numbers, choosing always one of the two available sub-categories in this row (becasue you wanted every category to be represented).
  2. The 6th formula in column G chooses one row from which the only left sub-category will be taken.
  3. The 7th formula in column G chooses another row from which the only left sub-category will be taken; it is more complicated to avoid duplicate with the choice from item 2.
  4. The rest is just converting numbers to real fruit names and sorting them into a random order, because the choices made previously have been made in a very nonradom order.

As you may notice, my solution relies heavily on the fact that there are always exactly two sub-categories in a row (so if we choose one in step 1, then we have no choice in step 2 and 3, if we select this row).

Adjusting this machinery to 99 rows, 75 columns and an (unknown to me) difference between 99 and the number of sub-categories you want to be selected (was 2 in the small example) is highly nontrivial. I suspect that the number of sub-categories varies form row to row in your real data - it would further complicate the situation.

I suggest that you send me the complete specification of your problem.

J.Ty.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,635
Members
414,083
Latest member
Mrsash

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