Non-Repeating Randomization

[aej]

Hello! I was wondering if there is a way to generate multiple columns of random numbers that may repeat within the column, but not within the row. Eg: 3 columns x 80 rows, using the numbers 1-9, where "1,7,7" would not be an acceptable row. It would also be helpful if I could tell Excel to include one number from 1-3 and 2 numbers from 6-9 per row. I know the basics of generating random numbers and non-repeating columns, but wasn't sure about how to automate creating this sort of matrix.

 

[Marc L]

​

Hi,​

​

as you forgot to indicate what kind of solution you are expecting for …​

 

[aej]

​

Hi,​

​

as you forgot to indicate what kind of solution you are expecting for …​

I'm not sure what you mean? I'd like to know if there's certain formula syntax or other techniques that I can use, or if I'm stuck doing it by hand.

 

[aej]

As an example, I'd like to be able to make something like this

 

[Marc L]

​

For a formula way let's wait for the Excel experts ! I may be back in case of any VBA way …​

 

[Habtest]

For three unique digits from 1-9:

Book1
	A	B	C
1	#1	#2	#3
2	3	6	4
3	2	7	6
4	5	6	2
Sheet1

Cell Formulas
Range	Formula
A2:A4	A2	=RANDBETWEEN(1,9)
B2:C4	B2	=LARGE(IF(COUNTIF($A2:A2,{1;2;3;4;5;6;7;8;9})=0,{1;2;3;4;5;6;7;8;9}),RANDBETWEEN(1,10-COLUMN()))

 

[aej]

For three unique digits from 1-9:

Book1
	A	B	C
1	#1	#2	#3
2	3	6	4
3	2	7	6
4	5	6	2
Sheet1

Cell Formulas
Range	Formula
A2:A4	A2	=RANDBETWEEN(1,9)
B2:C4	B2	=LARGE(IF(COUNTIF($A2:A2,{1;2;3;4;5;6;7;8;9})=0,{1;2;3;4;5;6;7;8;9}),RANDBETWEEN(1,10-COLUMN()))

Thanks so much!

 

[xslim12]

...

 

[xslim12]

For three unique digits from 1-9:

Book1
	A	B	C
1	#1	#2	#3
2	3	6	4
3	2	7	6
4	5	6	2
Sheet1

Cell Formulas
Range	Formula
A2:A4	A2	=RANDBETWEEN(1,9)
B2:C4	B2	=LARGE(IF(COUNTIF($A2:A2,{1;2;3;4;5;6;7;8;9})=0,{1;2;3;4;5;6;7;8;9}),RANDBETWEEN(1,10-COLUMN()))

Can you help walk me through the formula and how it works.