Randomised 7 patterns out of 9

[Kishan]

Randomised 7 patterns out of 9</SPAN></SPAN>

Using Excel 2000</SPAN></SPAN>
Hi,</SPAN></SPAN>

VBA, which can choose "7 random patterns" out of 9 from a My List of column A, and make a list of random in the 7 columns E:K as number of random rows are set in the cell B1</SPAN></SPAN>

Example data show 12 random as per B1 value </SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	My List	12			P1	P2	P3	P4	P5	P6	P7
2	1|1				1|1	2|2	2|2	2|X	1|1	2|2	X|1
3	1|X				X|1	1|2	2|1	X|2	X|1	2|1	1|X
4	X|1				2|1	2|2	1|1	X|2	X|X	2|1	1|X
5	1|2				X|X	2|X	X|2	1|X	X|2	1|2	X|2
6	2|1				2|1	2|1	X|1	1|1	2|1	2|X	X|1
7	X|2				2|1	1|X	X|2	X|1	1|1	X|X	1|X
8	2|X				2|2	X|1	2|X	2|2	2|1	1|1	2|1
9	X|X				1|1	1|2	1|2	1|1	2|1	2|1	2|X
10	2|2				2|2	2|2	1|1	1|2	2|X	2|2	2|1
11					1|X	1|X	X|X	X|X	X|X	X|X	2|1
12					1|2	X|X	2|1	X|1	1|1	1|2	1|X
13					1|2	2|X	X|X	X|1	1|2	2|1	1|1
14
15
16
Sheet5

Thank you in advance</SPAN></SPAN>

Regards,</SPAN>
Kishan</SPAN></SPAN>

 

[Gerald Higgins]

Here's a non VBA solution.

Insert this formula into cell E2 and copy across to K2 and down as far as required

=IF((ROW()+1)<=$B$1,INDEX($A$2:$A$10,ROUNDDOWN(L2*9,0)+1),"")

Test this before relying on it.

 

[Kishan]

Here's a non VBA solution.

Insert this formula into cell E2 and copy across to K2 and down as far as required

=IF((ROW()+1)<=$B$1,INDEX($A$2:$A$10,ROUNDDOWN(L2*9,0)+1),"")

Test this before relying on it.

Hi Gerald Higgins, I am getting results as shown below, done as you explain (Insert this formula into cell E2 and copy across to K2 and down as far as required)</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	My List	12			P1	P2	P3	P4	P5	P6	P7
2	1|1				1|1	1|1	1|1	1|1	1|1	1|1	1|1
3	1|X				1|1	1|1	1|1	1|1	1|1	1|1	1|1
4	X|1				1|1	1|1	1|1	1|1	1|1	1|1	1|1
5	1|2				1|1	1|1	1|1	1|1	1|1	1|1	1|1
6	2|1				1|1	1|1	1|1	1|1	1|1	1|1	1|1
7	X|2				1|1	1|1	1|1	1|1	1|1	1|1	1|1
8	2|X				1|1	1|1	1|1	1|1	1|1	1|1	1|1
9	X|X				1|1	1|1	1|1	1|1	1|1	1|1	1|1
10	2|2				1|1	1|1	1|1	1|1	1|1	1|1	1|1
11					1|1	1|1	1|1	1|1	1|1	1|1	1|1
12
13
14
15
16
Sheet5

Cell Formulas
Range	Formula
E2	=IF((ROW()+1)<=$B$1,INDEX($A$2:$A$10,ROUNDDOWN(L2*9,0)+1),"")

Thank you for your help</SPAN></SPAN>

Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[Gerald Higgins]

Sorry, there was a mistake in my post.

Try this . . .

=IF((ROW()+1)<=$B$1,INDEX($A$2:$A$10,ROUNDDOWN(RAND()*9,0)+1),"")

 

[Kishan]

Sorry, there was a mistake in my post.

Try this . . .

=IF((ROW()+1)<=$B$1,INDEX($A$2:$A$10,ROUNDDOWN(RAND()*9,0)+1),"")

Hi Gerald Higgins, this formula worked great, only noticed it was not generating combinations as per B1 value (2 minus then B1 value) I made small change +1 changed to -1 </SPAN></SPAN>

=IF((ROW()-1)<=$B$1,INDEX($A$2:$A$10,ROUNDDOWN(RAND()*9,0)+1),"")</SPAN></SPAN>

Thank you very much for your time and help</SPAN></SPAN>

Have a nice weekend</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>