Need to randomly combine numbers

[petkovst]

Hello guys,
I have 18 numbers (not from 1 to 18) which I need to randomly combine with each other in groups of 6.
Will appreciate any help on that.

Have a good one!

 

[wideboydixon]

Hi. You can achieve this with RAND or RANDBETWEEN on the worksheet but that's a volatile function so it will re-calculate the groups each time you change the value in any other cell. If that's not acceptable then you'll need to use VBA to create the groups. Which do you prefer?

WBD

 

[AlanY]

original 18 in column A, random combined in Col D in 3x6

try this

Excel 2012
	A	B	C	D
1	365	0.015254	18	500
2	108	0.465486	11	208
3	75	0.31014	16	192
4	1	0.826492	4	1
5	265	0.69175	6	422
6	233	0.632774	8	265
7	7	0.460137	12	284
8	192	0.827033	3	233
9	500	0.908309	1	296
10	208	0.887049	2	24
11	296	0.589543	9	108
12	318	0.34369	15	7
13	24	0.498427	10	456
14	66	0.345126	14	66
15	284	0.66784	7	318
16	456	0.394275	13	75
17	422	0.693502	5	140
18	140	0.07961	17	365
Sheet1

Cell Formulas
Range	Formula
B1	=RAND()
C1	=RANK(B1,$B$1:$B$18)
D1	=INDEX($A$1:$A$18,MATCH(ROW(C1),$C$1:$C$18,0))

 

[petkovst]

Well, I would like to be able to regenerate them periodically (students tests).

 

[wideboydixon]

Book1
	A	B	C	D	E
1	365	0.224007	11	456	1
2	108	0.035846	17	140
3	75	0.086352	16	66
4	1	0.164264	14	208
5	265	0.179736	12	233
6	233	0.573201	5	7
7	7	0.510665	6	318
8	192	0.032195	18	284
9	500	0.403336	9	500
10	208	0.670221	4	422
11	296	0.166137	13	365
12	318	0.476179	7	265
13	24	0.090182	15	296
14	66	0.803286	3	1
15	284	0.43613	8	24
16	456	0.908631	1	75
17	422	0.31553	10	108
18	140	0.809128	2	192
Sheet1

Cell Formulas
Range	Formula
B1	=MyRand($E$1, ROW())
C1	=RANK($B1,$B$1:$B$18)
D1	=INDEX($A$1:$A$18,MATCH(ROW(),$C$1:$C$18,0))

Public Function MyRand(seed As Long, occurrence As Long)

MyRand = Rnd(-seed)
Do While occurrence > 1
    MyRand = Rnd
    occurrence = occurrence - 1
Loop

End Function

Change the value in E1 to generate a new list.

WBD