Non-Repeating Random Selection of An Array

[jworkman7]

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.]

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

J.Ty.

 

[J.Ty.]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Apple	Apple1	Apple2		Watermelon1		2	Apple2	18	3	1	4	6
2	Orange	Orange1	Orange2		Pineapple1		1	Orange1	39	5	1	6	5
3	Watermelon	Watermelon1	Watermelon2		Banana2		2	Watermelon2	32	4	1	5	4
4	Banana	Banana1	Banana2		Apple2		2	Banana2	17	2	1	3	1
5	Pineapple	Pineapple1	Pineapple2		Watermelon2		1	Pineapple1	15	1	1	2	3
6					Orange1		3	Watermelon1	13	0	1	1	2
7					Pineapple2		4	Pineapple2	65	6	1	7	7
Sheet1

Cell Formulas
Range	Formula
G1	=RANDBETWEEN(1,2)
G2	=RANDBETWEEN(1,2)
G3	=RANDBETWEEN(1,2)
G4	=RANDBETWEEN(1,2)
G5	=RANDBETWEEN(1,2)
G6	=RANDBETWEEN(1,5)
G7	=RANDBETWEEN(1,4)
H1	=INDEX(B1:C1,G1)
H2	=INDEX(B2:C2,G2)
H3	=INDEX(B3:C3,G3)
H4	=INDEX(B4:C4,G4)
H5	=INDEX(B5:C5,G5)
H6	=INDEX(B1:C5,G6,IF(INDEX(G1:G5,G6)=2,1,2))
H7	=INDEX(B1:C5,IF(G7>=G6,G7+1,G7),IF(INDEX(G1:G5,IF(G7>=G6,G7+1,G7))=2,1,2))
I1	=RANDBETWEEN(1,100)
I2	=RANDBETWEEN(1,100)
I3	=RANDBETWEEN(1,100)
I4	=RANDBETWEEN(1,100)
I5	=RANDBETWEEN(1,100)
I6	=RANDBETWEEN(1,100)
I7	=RANDBETWEEN(1,100)
J1	=COUNTIF($I$1:$I$7,"<"&I1)
J2	=COUNTIF($I$1:$I$7,"<"&I2)
J3	=COUNTIF($I$1:$I$7,"<"&I3)
J4	=COUNTIF($I$1:$I$7,"<"&I4)
J5	=COUNTIF($I$1:$I$7,"<"&I5)
J6	=COUNTIF($I$1:$I$7,"<"&I6)
J7	=COUNTIF($I$1:$I$7,"<"&I7)
K1	=COUNTIF($I$1:$I1,"="&I1)
K2	=COUNTIF($I$1:$I2,"="&I2)
K3	=COUNTIF($I$1:$I3,"="&I3)
K4	=COUNTIF($I$1:$I4,"="&I4)
K5	=COUNTIF($I$1:$I5,"="&I5)
K6	=COUNTIF($I$1:$I6,"="&I6)
K7	=COUNTIF($I$1:$I7,"="&I7)
L1	=J1+K1
L2	=J2+K2
L3	=J3+K3
L4	=J4+K4
L5	=J5+K5
L6	=J6+K6
L7	=J7+K7
M1	=MATCH(ROW(),$L$1:$L$7,0)
M2	=MATCH(ROW(),$L$1:$L$7,0)
M3	=MATCH(ROW(),$L$1:$L$7,0)
M4	=MATCH(ROW(),$L$1:$L$7,0)
M5	=MATCH(ROW(),$L$1:$L$7,0)
M6	=MATCH(ROW(),$L$1:$L$7,0)
M7	=MATCH(ROW(),$L$1:$L$7,0)
E1	=INDEX($H$1:$H$7,M1)
E2	=INDEX($H$1:$H$7,M2)
E3	=INDEX($H$1:$H$7,M3)
E4	=INDEX($H$1:$H$7,M4)
E5	=INDEX($H$1:$H$7,M5)
E6	=INDEX($H$1:$H$7,M6)
E7	=INDEX($H$1:$H$7,M7)

 

[jworkman7]

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]

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.]

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.