rand between

[excelNewbie22]

hi,
i want to generate a random number/s from a list/range c2:h3,
but,
a-i got error of #ref
b-how to i choose the amount of rand numbers returned?
c-is it possible to eliminate duplicates based on the second range? c5:h6
(if in j2 i got 5 i want exclude it in j5)

test
	C	D	E	F	G	H	I	J
2	1	2	3	4	5	6		#REF!
3	7	8	9	10	11	12
4
5	1	2	3	4	5	6		#REF!
6	7	8	9	10	11	12
test

Cell Formulas
Range	Formula
J2,J5	J2	=INDEX(C2:H3, RANDBETWEEN(1, 6))

 

[DanteAmor]

Try:

Dante Amor
	C	D	E	F	G	H	I	J
2	1	2	3	4	5	6		8
3	7	8	9	10	11	12
4
5	1	2	3	4	5	6		7
6	7	8	9	10	11	12
Hoja10

Cell Formulas
Range	Formula
J2,J5	J2	=INDEX(C2:H3, RANDBETWEEN(1,2),RANDBETWEEN(1,6))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2			1	2	3	4	5	6		2
3			7	8	9	10	11	12
4
5			1	2	3	4	5	6		8
6			7	8	9	10	11	12
Main

Cell Formulas
Range	Formula
J2	J2	=INDEX(TOCOL(C2:H3,1),RANDBETWEEN(1,2))
J5	J5	=LET(a,TOCOL(C5:H6,1),INDEX(FILTER(a,a<>J2),RANDBETWEEN(1,11)))

 

[excelNewbie22]

danteamor, it return sometimes the same in j2 and j5,
fluff, it works but in j2 it always return 1 or 2, nothing else

also, how to choose how many numbers returned in the formula?

 

[Fluff]

The 2 in randbetween should be a 12

 

[excelNewbie22]

perfect,
anyway to eliminate duplicates more then one number with your formula here? range maybe? like j2:j5 or k2:n2 ?

=LET(a,TOCOL(C5:H6,1),INDEX(FILTER(a,a<>J2),RANDBETWEEN(1,11)))

and regarding the amount of choosing?

 

[Fluff]

How about

=LET(a,TOCOL(C5:H6,1),f,FILTER(a,ISNA(MATCH(a,J2:J3,0))),INDEX(a,RANDBETWEEN(1,ROWS(f))))

 

[excelNewbie22]

didn't worked, why?

test
	C	D	E	F	G	H	I	J	K	L
2	1	2	3	4	5	6		7	7	1
3	7	8	9	10	11	12
4	1	2	3	4	5	6
5	7	8	9	10	11	12
6	1	2	3	4	5	6
7	7	8	9	10	11	12
8	1	2	3	4	5	6
test

Cell Formulas
Range	Formula
J2	J2	=INDEX(TOCOL(C3:H4,1),RANDBETWEEN(1,2))
K2	K2	=LET(a,TOCOL(C5:H6,1),f,FILTER(a,ISNA(MATCH(a,J2:J2,0))),INDEX(a,RANDBETWEEN(1,ROWS(f))))
L2	L2	=LET(a,TOCOL(C7:H8,1),f,FILTER(a,ISNA(MATCH(a,J2:K2,0))),INDEX(a,RANDBETWEEN(1,ROWS(f))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:O2	Cell Value	duplicates	text	NO

 

[Fluff]

It should be

=LET(a,TOCOL(C5:H6,1),f,FILTER(a,ISNA(MATCH(a,J2:J2,0))),INDEX(f,RANDBETWEEN(1,ROWS(f))))

 

[excelNewbie22]