Randomly assign a range of numbers across the same number of cells

[Noobest]

I have written a script (my first) to generate the numbers 2-12, and assign these values in random order across a range of cells. My code works most of the time, but occasionally two instances of the same number occur. Please can someone help me improve my code? Thanks.

Row 1 and Column A are =RAND() and will be hidden.
Row 2 and Column B are as shown here. These should contain the values 2..12 in a random order.
As you can see, Column B has two 6s and no 9.

Help much appreciated, thanks.

 

[Fluff]

Hi & welcome to MrExcel.
One way

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2			4	10	8	9	11	5	6	3	12	2	7
3		9
4		10
5		2
6		12
7		6
8		3
9		8
10		7
11		4
12		5
13		11
14
Record

Cell Formulas
Range	Formula
C2:M2	C2	=INDEX(UNIQUE(RANDARRAY(100,,2,12,1)),SEQUENCE(,11))
B3:B13	B3	=INDEX(UNIQUE(RANDARRAY(100,,2,12,1)),SEQUENCE(11))
Dynamic array formulas.

 

[Fluff]

Thinking about it, this is a simpler way

=SORTBY(SEQUENCE(11,,2),RANDARRAY(11))

and

=SORTBY(SEQUENCE(,11,2),RANDARRAY(,11))

 

[Noobest]

Thank you I have tried both suggestions but I get Name Error. Quite possibly because I erred about my Excel version - its not 365, its Office Professional Plus 2019. Does that make a difference please? Or am I doing something incorrectly? Thanks again.

 

[lrobbo314]

Another option.

FYTD_App_by_Citizenship_T8_+_T42 (51).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			0.7	0.9	0.4	0.3	1.0	0.5	0.2	0.1	0.5	0.5	0.2
2		x	4	3	8	9	2	7	10	12	5	6	11
3	0.7	2
4	0.3	8
5	0.3	6
6	0.2	9
7	0.0	12
8	0.1	11
9	0.7	3
10	0.5	5
11	0.3	7
12	0.6	4
13	0.2	10
Sheet2

Cell Formulas
Range	Formula
C1:M1,A3:A13	C1	=RAND()
C2:M2	C2	=RANK(C1,$C$1:$M$1)+COUNTIF($C$1:C1,C1)
B3:B13	B3	=RANK(A3,$A$3:$A$13)+COUNTIF($A$3:A3,A3)

 

[Fluff]

Those functions don't work on 2019, they're only available with xl 365.
Another option would be

+Fluff 1.xlsm
	A	B
1
2
3	0.028961	12
4	0.239751	7
5	0.791572	4
6	0.094442	9
7	0.087848	10
8	0.809851	3
9	0.552833	5
10	0.045233	11
11	0.369495	6
12	0.146713	8
13	0.879692	2
Record

Cell Formulas
Range	Formula
A3:A13	A3	=RAND()
B3:B13	B3	=RANK(A3,$A$3:$A$13)+1

 

[Noobest]

Thank you so much! Sorted

 

[Fluff]

Glad we could help & thanks for the feedback.