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

 

[wjeil]

sorry forgot to mention. I have 3 columns (3 product places)

 

[Fluff]

Still works for me with 3 columns

+Fluff 1.xlsm
	A	B	C	D	E			H	I	J
1	A1	B1	C1					Product 1	Product 2	Product 3
2	A2	B2	C2					A34	A7	A29
3	A3	B3	C3					A49	A40	A16
4	A4	B4	C4					A24	A1	A12
5	A5	B5	C5					A60	A56	A2
6	A6	B6	C6					A41	A22	A59
7	A7	B7	C7					A17	A57	A51
8	A8	B8	C8					A6	A26	A19
9	A9	B9	C9					A32	A39	A20
10	A10	B10	C10					A53	A44	A35
11	A11	B11	C11					A30	A5	A61
12	A12	B12	C12					A18	A52	A8
13	A13	B13	C13					A23	A13	A15
14	A14	B14	C14					A46	A14	A31
15	A15	B15	C15					A63	A36	A43
16	A16	B16	C16					A50	A21	A58
17	A17	B17	C17					A38	A45	A55
18	A18	B18	C18					A11	A3	A10
19	A19	B19	C19					A62	A4	A47
20	A20	B20	C20					A25	A48	A37
21	A21	B21	C21					A54	A33	A9
22	A22	B22	C22					A27	A42	A28
23	A23	B23	C23					B55	B56	B15
24	A24	B24	C24					B52	B58	B27
25	A25	B25	C25					B62	B81	B40
26	A26	B26	C26					B5	B33	B84
27	A27	B27	C27					B48	B13	B1
28	A28	B28	C28					B51	B44	B70
29	A29	B29	C29					B9	B12	B61
30	A30	B30	C30					B74	B53	B80
31	A31	B31	C31					B21	B7	B57
32	A32	B32	C32					B88	B68	B79
33	A33	B33	C33					B20	B67	B95
34	A34	B34	C34					B63	B26	B77
35	A35	B35	C35					B6	B17	B14
36	A36	B36	C36					B37	B38	B75
37	A37	B37	C37					B31	B45	B78
38	A38	B38	C38					B41	B30	B18
39	A39	B39	C39					B85	B76	B24
40	A40	B40	C40					B23	B49	B86
41	A41	B41	C41					B4	B47	B93
42	A42	B42	C42					B2	B72	B29
43	A43	B43	C43					B35	B89	B25
44	A44	B44	C44					B8	B73	B11
45	A45	B45	C45					B60	B19	B69
46	A46	B46	C46					B54	B82	B32
47	A47	B47	C47					B83	B22	B36
48	A48	B48	C48					B34	B59	B94
49	A49	B49	C49					B66	B42	B50
50	A50	B50	C50					B91	B43	B64
51	A51	B51	C51					B10	B92	B46
52	A52	B52	C52					B39	B71	B16
53	A53	B53	C53					B90	B87	B28
54	A54	B54	C54					B65	B3
55	A55	B55	C55					C61	C185	C126
56	A56	B56	C56					C78	C91	C182
57	A57	B57	C57					C121	C24	C20
58	A58	B58	C58					C30	C117	C85
59	A59	B59	C59					C34	C89	C57
60	A60	B60	C60					C188	C122	C49
61	A61	B61	C61					C100	C114	C179
62	A62	B62	C62					C3	C176	C134
63	A63	B63	C63					C74	C72	C22
64		B64	C64					C143	C2	C14
65		B65	C65					C155	C40	C79
66		B66	C66					C19	C28	C119
67		B67	C67					C181	C41	C159
68		B68	C68					C154	C7	C39
69		B69	C69					C111	C31	C23
70		B70	C70					C62	C190	C96
71		B71	C71					C175	C5	C43
72		B72	C72					C67	C115	C136
73		B73	C73					C105	C81	C166
74		B74	C74					C48	C112	C18
75		B75	C75					C44	C36	C69
76		B76	C76					C17	C140	C108
77		B77	C77					C120	C158	C145
78		B78	C78					C99	C160	C1
79		B79	C79					C9	C87	C77
80		B80	C80					C92	C32	C59
81		B81	C81					C133	C129	C167
82		B82	C82					C152	C38	C6
83		B83	C83					C142	C149	C4
84		B84	C84					C174	C153	C75
85		B85	C85					C193	C83	C10
86		B86	C86					C66	C63	C35
87		B87	C87					C192	C135	C71
88		B88	C88					C51	C65	C146
89		B89	C89					C27	C47	C172
90		B90	C90					C151	C191	C80
91		B91	C91					C110	C131	C144
92		B92	C92					C127	C139	C26
93		B93	C93					C29	C183	C68
94		B94	C94					C82	C90	C102
95		B95	C95					C94	C98	C156
96			C96					C58	C186	C33
97			C97					C8	C101	C106
98			C98					C60	C52	C56
99			C99					C116	C64	C124
100			C100					C161	C147	C12
101			C101					C173	C93	C171
102			C102					C132	C128	C55
103			C103					C169	C50	C46
104			C104					C178	C180	C73
105			C105					C13	C177	C11
106			C106					C25	C113	C118
107			C107					C138	C84	C123
108			C108					C76	C86	C109
109			C109					C15	C148	C95
110			C110					C37	C104	C103
111			C111					C97	C162	C130
112			C112					C42	C88	C189
113			C113					C163	C70	C184
114			C114					C141	C164	C16
115			C115					C54	C170	C137
116			C116					C107	C168	C53
117			C117					C157	C125	C187
118			C118					C150	C45	C21
119			C119					C165
120			C120
Primary

Cell Formulas
Range	Formula
A1:A63	A1	="A"&SEQUENCE(63)
B1:B95	B1	="B"&SEQUENCE(95)
C1:C193	C1	="C"&SEQUENCE(193)
H2:J119	H2	=LET(a,A1#,b,B1#,c,C1#,Prod,3,ra,CEILING(ROWS(a),Prod),rb,CEILING(ROWS(b),Prod),rc,CEILING(ROWS(c),Prod),sProd,SEQUENCE(,Prod,0),s,SEQUENCE((ra+rb+rc)/Prod,,,Prod),IFERROR(IF(s<=ra,INDEX(SORTBY(a,RANDARRAY(ROWS(a))),s+sProd),IF(s<=ra+rb,INDEX(SORTBY(b,RANDARRAY(ROWS(b))),(s-ra)+sProd),INDEX(SORTBY(c,RANDARRAY(ROWS(c))),s-ra-rb+sProd))),""))
Dynamic array formulas.

 

[wjeil]

It works! I had to translate the ceilling function, and used the wrong one.

Thank you very much for all your help.

 

[Fluff]

Glad it's sorted & thanks for the feedback.
To translate formulae I tend to use this site, which is normally very good. Translator