Non-Repeating Random Selection of An Array

jworkman7 · Mar 23, 2016

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?

J.Ty. · Mar 23, 2016

Do you want all selections satisfying your criteria to be equiprobable?

J.Ty.

J.Ty. · Mar 23, 2016

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.

Excel 2010<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>Sheet1 <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" >Worksheet Formulas<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(1,2)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H1</th><td style="text-align:left">=INDEX(B1:C1,G1)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I1</th><td style="text-align:left">=RANDBETWEEN(1,100)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J1</th><td style="text-align:left">=COUNTIF($I$1:$I$7,"<"&I1)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K1</th><td style="text-align:left">=COUNTIF($I$1:$I1,"="&I1)</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(ROW(),$L$1:$L$7,0)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G2</th><td style="text-align:left">=RANDBETWEEN(1,2)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H2</th><td style="text-align:left">=INDEX(B2:C2,G2)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I2</th><td style="text-align:left">=RANDBETWEEN(1,100)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J2</th><td style="text-align:left">=COUNTIF($I$1:$I$7,"<"&I2)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K2</th><td style="text-align:left">=COUNTIF($I$1:$I2,"="&I2)</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(ROW(),$L$1:$L$7,0)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G3</th><td style="text-align:left">=RANDBETWEEN(1,2)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H3</th><td style="text-align:left">=INDEX(B3:C3,G3)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I3</th><td style="text-align:left">=RANDBETWEEN(1,100)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J3</th><td style="text-align:left">=COUNTIF($I$1:$I$7,"<"&I3)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K3</th><td style="text-align:left">=COUNTIF($I$1:$I3,"="&I3)</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(ROW(),$L$1:$L$7,0)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G4</th><td style="text-align:left">=RANDBETWEEN(1,2)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H4</th><td style="text-align:left">=INDEX(B4:C4,G4)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I4</th><td style="text-align:left">=RANDBETWEEN(1,100)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J4</th><td style="text-align:left">=COUNTIF($I$1:$I$7,"<"&I4)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K4</th><td style="text-align:left">=COUNTIF($I$1:$I4,"="&I4)</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(ROW(),$L$1:$L$7,0)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G5</th><td style="text-align:left">=RANDBETWEEN(1,2)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H5</th><td style="text-align:left">=INDEX(B5:C5,G5)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I5</th><td style="text-align:left">=RANDBETWEEN(1,100)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J5</th><td style="text-align:left">=COUNTIF($I$1:$I$7,"<"&I5)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K5</th><td style="text-align:left">=COUNTIF($I$1:$I5,"="&I5)</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(ROW(),$L$1:$L$7,0)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G6</th><td style="text-align:left">=RANDBETWEEN(1,5)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H6</th><td style="text-align:left">=INDEX(B1:C5,G6,IF(INDEX(G1:G5,G6)=2,1,2))</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I6</th><td style="text-align:left">=RANDBETWEEN(1,100)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J6</th><td style="text-align:left">=COUNTIF($I$1:$I$7,"<"&I6)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K6</th><td style="text-align:left">=COUNTIF($I$1:$I6,"="&I6)</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(ROW(),$L$1:$L$7,0)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G7</th><td style="text-align:left">=RANDBETWEEN(1,4)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H7</th><td style="text-align:left">=INDEX(B1:C5,IF(G7>=G6,G7+1,G7),IF(INDEX(G1:G5,IF(G7>=G6,G7+1,G7))=2,1,2))</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I7</th><td style="text-align:left">=RANDBETWEEN(1,100)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J7</th><td style="text-align:left">=COUNTIF($I$1:$I$7,"<"&I7)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K7</th><td style="text-align:left">=COUNTIF($I$1:$I7,"="&I7)</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(ROW(),$L$1:$L$7,0)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E1</th><td style="text-align:left">=INDEX($H$1:$H$7,M1)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">=INDEX($H$1:$H$7,M2)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E3</th><td style="text-align:left">=INDEX($H$1:$H$7,M3)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E4</th><td style="text-align:left">=INDEX($H$1:$H$7,M4)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E5</th><td style="text-align:left">=INDEX($H$1:$H$7,M5)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E6</th><td style="text-align:left">=INDEX($H$1:$H$7,M6)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E7</th><td style="text-align:left">=INDEX($H$1:$H$7,M7)</td></tr></tbody></table></td></tr></table>

jworkman7 · Mar 24, 2016

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 · Mar 24, 2016

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. · Mar 24, 2016

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:

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).
The 6th formula in column G chooses one row from which the only left sub-category will be taken.
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.
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.

Non-Repeating Random Selection of An Array

jworkman7

Board Regular

Some videos you may like

Excel Facts

J.Ty.

Well-known Member

J.Ty.

Well-known Member

jworkman7

Board Regular

jworkman7

Board Regular

J.Ty.

Well-known Member

Similar threads

Watch MrExcel Video

Forum statistics

Share this page

Non-Repeating Random Selection of An Array

jworkman7

Board Regular

Some videos you may like

Excel Facts

J.Ty.

Well-known Member

J.Ty.

Well-known Member

jworkman7

Board Regular

jworkman7

Board Regular

J.Ty.

Well-known Member

Similar threads

Watch MrExcel Video

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock