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

[#2 · Accepted solution]

Hi @hemakumarmech24 

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)
)

[#3]

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..

[#4]

Glad I could help & Thanks for providing feedback

[#5]

Hi @hemakumarmech24 

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)
        )
    )
)