Random select 3 values per row from specific range without duplicates

[wjeil]

Hello,

Im trying to randomly assign products to a certain location randomly. Locations consist of 3 products and are either for an A, B or C product. Therefore it is not possible to have any duplictates. How can I randomly assign products to a certain location so that every product is stored and that there are not any duplicates?

Is there a solutions, VBA or just with formulas, to model this in Excel?

Regards,
Willem

 

[BSALV]

for the A-products, a named range "Mijn_A" and then as many randum values as products A, rank them and assign them

Map1
	A	B	C	D	E	F
1	A1	2	0,83439	A12	A1	A3
2	A2	4	0,82160	A2	A4	A5
3	A3	3	0,82590	A10	A8	A9
4	A4	5	0,77260	A11	A7	A6
5	A5	6	0,65756
6	A6	12	0,06282
7	A7	11	0,24176
8	A8	8	0,48688
9	A9	9	0,44886
10	A10	7	0,60733
11	A11	10	0,31605
12	A12	1	0,91870
Blad2

Cell Formulas
Range	Formula
C1:C12	C1	=RANDARRAY(ROWS(Mijn_A))
D1:F4	D1	=INDEX(Mijn_A,MATCH(SEQUENCE(4,3),OFFSET(Mijn_A,,1,,),0))
B1:B12	B1	=RANK(C1,OFFSET($C$1,,,ROWS(Mijn_A),))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Mijn_A	=Blad2!$A$1:$A$12	B1:B12, C1:D1

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	A1	B1	C1
2	A2	B2	C2				Loc1	A	A8	A3	A4
3	A3	B3	C3				Loc2	A	A10	A12	A6
4	A4	B4	C4				Loc3	A	A2	A7	A9
5	A5	B5	C5				Loc4	B	A1	A5	A11
6	A6	B6	C6				Loc5	B	B2	B5	B18
7	A7	B7	C7				Loc6	B	B7	B12	B8
8	A8	B8	C8				Loc7	B	B4	B17	B6
9	A9	B9	C9				Loc8	B	B9	B3	B13
10	A10	B10	C10				Loc9	B	B14	B11	B1
11	A11	B11	C11				Loc10	B	B16	B15	B10
12	A12	B12	C12				Loc11	C	C9	C4	C3
13		B13	C13				Loc12	C	C5	C21	C23
14		B14	C14				Loc13	C	C7	C16	C6
15		B15	C15				Loc14	C	C18	C20	C19
16		B16	C16				Loc15	C	C17	C10	C2
17		B17	C17				Loc16	C	C24	C15	C13
18		B18	C18				Loc17	C	C12	C11	C8
19			C19				Loc18	C	C14	C22	C1
20			C20
21			C21
22			C22
23			C23
24			C24
25
Primary

Cell Formulas
Range	Formula
I2:K19	I2	=LET(a,SORTBY(A1:A12,RANDARRAY(12)),b,SORTBY(B1:B18,RANDARRAY(18)),c,SORTBY(C1:C24,RANDARRAY(24)),ra,ROWS(a),rb,ROWS(b),rc,ROWS(c),s,SEQUENCE((ra+rb+rc)/3,,,3),IF(s<=ra,INDEX(a,s+{0,1,2}),IF(s<=ra+rb,INDEX(b,(s-ra)+{0,1,2}),INDEX(c,s-ra-rb+{0,1,2}))))
Dynamic array formulas.

 

[Fluff]

Another option, slightly longer, but easier to maintain & deals with blank cells if there are no enough products.

+Fluff 1.xlsm
	A	B	C	D			G	H	I	J	K	L	M	N	O	P	Q	R
1	A1	B1	C1						Product 1	Product 2	Product 3				Product 1	Product 2	Product 3	Product 4
2	A2	B2	C2				Loc1	A	A11	A7	A4		Loc1	A	A3	A4	A5	A9
3	A3	B3	C3				Loc2	A	A8	A1	A2		Loc2	A	A1	A10	A12	A7
4	A4	B4	C4				Loc3	A	A12	A10	A9		Loc3	A	A2	A6	A8	A11
5	A5	B5	C5				Loc4	B	A5	A3	A6		Loc4	B	B12	B14	B10	B6
6	A6	B6	C6				Loc5	B	B7	B11	B18		Loc5	B	B18	B17	B2	B1
7	A7	B7	C7				Loc6	B	B12	B15	B1		Loc6	B	B9	B3	B11	B7
8	A8	B8	C8				Loc7	B	B4	B10	B17		Loc7	B	B16	B5	B8	B15
9	A9	B9	C9				Loc8	B	B14	B5	B13		Loc8	B	B13	B4
10	A10	B10	C10				Loc9	B	B16	B6	B9		Loc9	C	C15	C18	C13	C5
11	A11	B11	C11				Loc10	B	B8	B3	B2		Loc10	C	C24	C22	C23	C16
12	A12	B12	C12				Loc11	C	C17	C9	C12		Loc11	C	C2	C20	C14	C17
13		B13	C13				Loc12	C	C18	C11	C2		Loc12	C	C7	C11	C8	C4
14		B14	C14				Loc13	C	C19	C10	C16		Loc13	C	C3	C1	C10	C6
15		B15	C15				Loc14	C	C22	C21	C4
16		B16	C16				Loc15	C	C3	C24	C23
17		B17	C17				Loc16	C	C7	C8	C1
18		B18	C18				Loc17	C	C15	C6	C13
19			C19				Loc18	C	C5	C14	C20
20			C20
21			C21
22			C22
23			C23
24			C24
25
Primary

Cell Formulas
Range	Formula
I2:K19	I2	=LET(a,A1:A12,b,B1:B18,c,C1:C24,Prod,3,ra,ROWS(a),rb,ROWS(b),rc,ROWS(c),sProd,SEQUENCE(,Prod,0),s,SEQUENCE((ra+rb+rc)/Prod,,,Prod),IFERROR(IF(s<=ra,INDEX(SORTBY(a,RANDARRAY(ra)),s+sProd),IF(s<=ra+rb,INDEX(SORTBY(b,RANDARRAY(rb)),(s-ra)+sProd),INDEX(SORTBY(c,RANDARRAY(rc)),s-ra-rb+sProd))),""))
O2:R14	O2	=LET(a,A1:A12,b,B1:B18,c,C1:C24,Prod,4,ra,ROWS(a),rb,ROWS(b),rc,ROWS(c),sProd,SEQUENCE(,Prod,0),s,SEQUENCE((ra+rb+rc)/Prod,,,Prod),IFERROR(IF(s<=ra,INDEX(SORTBY(a,RANDARRAY(ra)),s+sProd),IF(s<=ra+rb,INDEX(SORTBY(b,RANDARRAY(rb)),(s-ra)+sProd),INDEX(SORTBY(c,RANDARRAY(rc)),s-ra-rb+sProd))),""))
Dynamic array formulas.

 

[wjeil]

It works like a charm thank you very much. I got 1 further question. How can adapt the line so that the first 3 locations randomly contain A1 till A12?

 

[Fluff]

Which formula are you talking about?

 

[wjeil]

O2

 

[Fluff]

In that case I don't understand what you are asking. The formula picks random cells from A1:A12 & fills them in the output without duplicates.

 

[wjeil]

Sorry if i'm unclear. What i was actually trying to say was how can i make this range dynamic? So if I have X A-products, X B products and X C-products, with each corresponding (e.g. if i have 20 A-products I have 7 locations available) locations. How can i make the range which is now A1:A12, B1:B18 and C:C24 towards A1:AX, B1:BX and C1:CX?

So with the assumption that i have enough locations for each product.

Next to that, in my model if SEQUENCE((ra+rb+rc)/Prod,,,Prod) is not an integer it rounds down instead of up.

Thanks for all your help in advance.

 

[Fluff]

Are the values in cols A, B & C spilled ranges?