SOLVED

# Fill unique values across columns but repeation allowed in columns

Copper Contributor

# Fill unique values across columns but repeation allowed in columns

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

4 Replies
best response confirmed by hemakumarmech24 (Copper Contributor)
Solution

# Re: Fill unique values across columns but repeation allowed in columns

Assuming your formula in C2 is =UNIQUE(B2:B11), in E2 and copy down as necessary:

``````=LET(
r, ROWS(C\$2#),
k, INDEX(SORTBY(SEQUENCE(r), RANDARRAY(r)),SEQUENCE(,4)),
INDEX(C\$2#,k)
)``````

# Re: Fill unique values across columns but repeation allowed in columns

Sir thanks for your immediate response.
It worked out pretty well as I expected.
Thanks once again for helping me out.
It means a lot..

# Re: Fill unique values across columns but repeation allowed in columns

Glad I could help & Thanks for providing feedback

# Re: Fill unique values across columns but repeation allowed in columns

If you have the LAMDBA & Co. functions, as a single array:

in E2:

``````=LET(
rws, ROWS(A2:A11),
cls, ROWS(C2#),
unq, TRANSPOSE(C2#),
rnd, MAKEARRAY(rws,cls,
LAMBDA(rw,cl,
INDEX(RANDARRAY(,cls),cl)
)
),
MAKEARRAY(rws,cls-1,
LAMBDA(rw,cl,
INDEX(SORTBY(unq,INDEX(rnd,rw)),cl)
)
)
)``````
1 best response

Accepted Solutions
best response confirmed by hemakumarmech24 (Copper Contributor)
Solution

# Re: Fill unique values across columns but repeation allowed in columns

Assuming your formula in C2 is =UNIQUE(B2:B11), in E2 and copy down as necessary:

``````=LET(
r, ROWS(C\$2#),
k, INDEX(SORTBY(SEQUENCE(r), RANDARRAY(r)),SEQUENCE(,4)),
INDEX(C\$2#,k)
)``````