Divide a number into multiple results

[excelNewbie22]

hi!

i have a dynamic range of number, every few days changes,
a2:a1000, or a2:a1515....
and i want to get random x numbers which will cover most of the range,
by getting the entire count of the range, say... 950, divides to say... 15, which will be 63 (round),
so i'll get 1 random number from each 62 cells,
so i did this:

sheet1
	A	B	C	D	E	F	G	H
1	950	divide to 15						vstack
2	1	63	so…	1	63		2	24
3	2			64	126		0	0
4	3			127	189
5	4			190	252
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	17
19	18
20	19
21	20
22	21
23	22
24	23
25	24
26	25
27	26
28	27
29	28
30	29
31	30
32	31
33	32
sheet1

Cell Formulas
Range	Formula
B2	B2	=ROUND(950/15,0)
H2:H3	H2	=VSTACK(TRANSPOSE(LET(u,UNIQUE(TOROW(A2:INDIRECT("a"&$B$2+2)),1),TAKE(SORTBY(u,RANDARRAY(,COLUMNS(u))),,1))),TRANSPOSE(LET(u,UNIQUE(TOROW(INDIRECT("a"&$B$2+3):INDIRECT("a"&$B$2*2+(2))),1),TAKE(SORTBY(u,RANDARRAY(,COLUMNS(u))),,1))))
G2	G2	=TRANSPOSE(LET(u,UNIQUE(TOROW(A2:INDIRECT("a"&$B$2+2)),1),TAKE(SORTBY(u,RANDARRAY(,COLUMNS(u))),,1)))
G3	G3	=TRANSPOSE(LET(u,UNIQUE(TOROW(INDIRECT("a"&$B$2+3):INDIRECT("a"&$B$2*2+(2))),1),TAKE(SORTBY(u,RANDARRAY(,COLUMNS(u))),,1)))
Dynamic array formulas.

but this is long, is there anything more clever? shorter? (the end result will be a much longer)

 

[Fluff]

How about

=LET(a,A2:A951,b,COUNT(a),c,FLOOR.MATH(b/15,1),DROP(REDUCE("",SEQUENCE(b/c,,,c),LAMBDA(x,y,VSTACK(x,TAKE(SORTBY(CHOOSEROWS(a,SEQUENCE(c,,y)),RANDARRAY(c)),1)))),1))

 

[excelNewbie22]

lovely!
thank you fluff!

 

[excelNewbie22]

a snag...
why the error?

sheet1
	A	B	C	D	E
3	2	1	2	3-2-3	#DIV/0!
4	3	2	3	4-3-4
5	4	3	4	5-4-5
6	5	4	5	6-5-6
7	6	5	6	7-6-7
8	7	6	7	8-7-8
9	8	7	8	9-8-9
10	9	8	9	10-9-10
11	10	9	10	11-10-11
12	11	10	11	12-11-12
13	12	11	12	13-12-13
14	13	12	13	14-13-14
15	14	13	14	15-14-15
16	15	14	15	16-15-16
17	16	15	16	17-16-17
18	17	16	17	18-17-18
19	18	17	18	19-18-19
20	19	18	19	20-19-20
21	20	19	20	21-20-21
22	21	20	21	22-21-22
23	22	21	22	23-22-23
24	23	22	23	24-23-24
25	24	23	24	25-24-25
sheet1

Cell Formulas
Range	Formula
E3	E3	=LET(a,D3:D24,b,COUNT(a),c,FLOOR.MATH(b/15,1),DROP(REDUCE("",SEQUENCE(b/c,,,c),LAMBDA(x,y,VSTACK(x,TAKE(SORTBY(CHOOSEROWS(a,SEQUENCE(c,,y)),RANDARRAY(c)),1)))),1))
D3:D25	D3	=A4&"-"&B4&"-"&C4

 

[Fluff]

You get the error as col D is text & not numbers, so Count will return 0, you would need to use COUNTA

 

[excelNewbie22]

so simple, thank you, again!

 

[Fluff]

My pleasure.

 

[djclements]

Alternatively, you could sort by group ID first (then by RANDARRAY) to achieve the same results:

=LET(
    a, TOCOL(D:D,1),
    n, 15,
    r, FLOOR.MATH(ROWS(a),n),
    i, r/n,
    CHOOSEROWS(SORTBY(TAKE(a,r),INT(SEQUENCE(r,,0)/i),1,RANDARRAY(r),1),SEQUENCE(n,,,i))
)

Please note, if your data column contains a header row, use the DROP function as follows:

=LET(
    a, DROP(TOCOL(D:D,1),1),
    ...
)