Fill unique values across columns but repeation allowed in columns

[hemakumarmech24]

Dear Members,

I just wanted to create MCQ Quiz sheet. I submitted a sample screen shot.

I found the unique values using unique command in column 'C'.

Now i wanted to fill option 2,3,4(i.e F2,G2,H2) from unique answers (column 'C'.)in random manner, with option 1 (E2) always being the answer I.e Cell B2. No repeation across columns but in rows repeation allowed.

I just tried rand, rand between, index,match function to create random values for this,I'm always getting either same value or ! Value error.

How to use the functions correctly in this scenario. PFA screenshot

thanks in advance

 

[Fluff]

Hi & welcome to MrExcel.
For xl 365 how about

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

Cell Formulas
Range	Formula
C2:C6	C2	=UNIQUE(B2:B11)
E2:E11	E2	=INDEX(B2:B11,)
F2:H11	F2	=LET(f,FILTER($C$2#,$C$2#<>E2),INDEX(SORTBY(f,RANDARRAY(ROWS(f))),{1,2,3}))
Dynamic array formulas.

 

[hemakumarmech24]

Dear sir

The formula worked perfectly fine.Thanks a lot sir., i need one more clarification.

if i add date in columns a and b, like shown in table . the unique column 'c' has to be dynamically updated and the options1,2,3,4,5 also dynamically updated up to last cells in column 'a' and 'b'. Hope you understand my english and Thanks for the immediate response much appreciated...

TEST.xlsx
	A	B	C	D	E	F	G	H	I
1	QUESTIONS	ANSWERS	UNIQUE		OPTION 1	OPTION 2	OPTION 3	OPTION 4	OPTION 5
2	1	A	A		A	B	C	E	D
3	2	B	B		B	D	A	C	E
4	3	C	C		C	E	D	A	B
5	4	D	D		D	E	B	A	C
6	5	E	E		E	A	D	B	C
7	6	D			D	A	C	E	B
8	7	C			C	D	E	A	B
9	8	A			A	E	C	B	D
10	9	B			B	E	D	A	C
11	10	C			C	A	B	E	D
12	11	X
13	12	Y
14	13	Z
15	14	X
16	15	Y
Sheet2

Cell Formulas
Range	Formula
C2:C6	C2	=UNIQUE(B2:B11)
E2:E11	E2	=INDEX(B2:B11,)
F2:I11	F2	=LET(f,FILTER($C$2#,$C$2#<>E2),INDEX(SORTBY(f,RANDARRAY(ROWS(f))),{1,2,3,4}))
Dynamic array formulas.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	QUESTIONS	ANSWERS	UNIQUE		OPTION 1	OPTION 2	OPTION 3	OPTION 4	OPTION 5
2	1	A	A		A	E	X	Y	B
3	2	B	B		B	D	E	A	X
4	3	C	C		C	A	D	E	B
5	4	D	D		D	A	C	B	X
6	5	E	E		E	X	Y	B	D
7	6	D	X		D	Y	B	E	X
8	7	C	Y		C	Y	X	E	A
9	8	A	Z		A	B	Z	X	Y
10	9	B			B	Z	X	E	D
11	10	C			C	A	B	D	E
12	11	X			X	Z	Y	B	D
13	12	Y			Y	Z	B	D	A
14	13	Z			Z	E	A	Y	C
15	14	X			X	C	A	Y	B
16	15	Y			Y	E	A	C	Z
17
18
19
20
21
Data

Cell Formulas
Range	Formula
C2:C9	C2	=UNIQUE(FILTER(B2:B100,B2:B100<>""))
E2:E16	E2	=FILTER(B2:B100,B2:B100<>"")
F17:F21,F2:I16	F2	=LET(f,FILTER($C$2#,$C$2#<>E2),IF(E2="","",INDEX(SORTBY(f,RANDARRAY(ROWS(f))),{1,2,3,4})))
Dynamic array formulas.

 

[hemakumarmech24]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	QUESTIONS	ANSWERS	UNIQUE		OPTION 1	OPTION 2	OPTION 3	OPTION 4	OPTION 5
2	1	A	A		A	E	X	Y	B
3	2	B	B		B	D	E	A	X
4	3	C	C		C	A	D	E	B
5	4	D	D		D	A	C	B	X
6	5	E	E		E	X	Y	B	D
7	6	D	X		D	Y	B	E	X
8	7	C	Y		C	Y	X	E	A
9	8	A	Z		A	B	Z	X	Y
10	9	B			B	Z	X	E	D
11	10	C			C	A	B	D	E
12	11	X			X	Z	Y	B	D
13	12	Y			Y	Z	B	D	A
14	13	Z			Z	E	A	Y	C
15	14	X			X	C	A	Y	B
16	15	Y			Y	E	A	C	Z
17
18
19
20
21
Data

Cell Formulas
Range	Formula
C2:C9	C2	=UNIQUE(FILTER(B2:B100,B2:B100<>""))
E2:E16	E2	=FILTER(B2:B100,B2:B100<>"")
F17:F21,F2:I16	F2	=LET(f,FILTER($C$2#,$C$2#<>E2),IF(E2="","",INDEX(SORTBY(f,RANDARRAY(ROWS(f))),{1,2,3,4})))
Dynamic array formulas.

Sir the highlighted cells not dynamically updating i.e F17,G17,I17

TEST.xlsx
	A	B	C	D	E	F	G	H	I
1	QUESTIONS	ANSWERS	UNIQUE		OPTION 1	OPTION 2	OPTION 3	OPTION 4	OPTION 5
2	1	A	A		A	X	E	C	D
3	2	B	B		B	A	C	X	Y
4	3	C	C		C	E	B	A	Y
5	4	D	D		D	C	E	aa	B
6	5	E	E		E	C	A	B	Y
7	6	D	X		D	B	C	E	A
8	7	C	Y		C	E	Y	B	A
9	8	A	Z		A	Z	C	B	X
10	9	B	aa		B	X	C	E	D
11	10	C			C	Y	X	aa	B
12	11	X			X	C	Z	Y	D
13	12	Y			Y	aa	Z	B	X
14	13	Z			Z	A	X	B	Y
15	14	X			X	B	Y	C	E
16	15	Y			Y	D	E	aa	B
17	16	aa			aa
Sheet2

Cell Formulas
Range	Formula
C2:C10	C2	=UNIQUE(FILTER(B2:B1000,B2:B1000<>""))
E2:E17	E2	=FILTER(B2:B1000,B2:B1000<>"")
F2:I16	F2	=LET(f,FILTER($C$2#,$C$2#<>E2),IF(E2="","",INDEX(SORTBY(f,RANDARRAY(ROWS(f))),{1,2,3,4})))
Dynamic array formulas.

 

[hemakumarmech24]

Sir the highlighted cells not dynamically updating i.e F17,G17,I17

TEST.xlsx
	A	B	C	D	E	F	G	H	I
1	QUESTIONS	ANSWERS	UNIQUE		OPTION 1	OPTION 2	OPTION 3	OPTION 4	OPTION 5
2	1	A	A		A	X	E	C	D
3	2	B	B		B	A	C	X	Y
4	3	C	C		C	E	B	A	Y
5	4	D	D		D	C	E	aa	B
6	5	E	E		E	C	A	B	Y
7	6	D	X		D	B	C	E	A
8	7	C	Y		C	E	Y	B	A
9	8	A	Z		A	Z	C	B	X
10	9	B	aa		B	X	C	E	D
11	10	C			C	Y	X	aa	B
12	11	X			X	C	Z	Y	D
13	12	Y			Y	aa	Z	B	X
14	13	Z			Z	A	X	B	Y
15	14	X			X	B	Y	C	E
16	15	Y			Y	D	E	aa	B
17	16	aa			aa
Sheet2

Cell Formulas
Range	Formula
C2:C10	C2	=UNIQUE(FILTER(B2:B1000,B2:B1000<>""))
E2:E17	E2	=FILTER(B2:B1000,B2:B1000<>"")
F2:I16	F2	=LET(f,FILTER($C$2#,$C$2#<>E2),IF(E2="","",INDEX(SORTBY(f,RANDARRAY(ROWS(f))),{1,2,3,4})))
Dynamic array formulas.

Dear sir
I dragged down the formula from F2:I100 and it solved the problem. Now everything is working as I expected.
Thank you a million times over for helping me out!

 

[Fluff]

Glad to help & thanks for the feedback.