Forum Discussion

hemakumarmech24

Copper Contributor

Mar 29, 2022

Solved

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

thanks in advance

excel

Formulas and Functions

office 365

Lorenzo
Mar 29, 2022
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) )

4 Replies

Lorenzo

Silver Contributor

Mar 30, 2022

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

Lorenzo
Silver Contributor
Mar 29, 2022
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) )
- hemakumarmech24
  Copper Contributor
  Mar 29, 2022
  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..
  - Lorenzo
    Silver Contributor
    Mar 29, 2022
    Glad I could help & Thanks for providing feedback