Forum Discussion

hemakumarmech24's avatar
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...
excel
Formulas and Functions
office 365
  • Lorenzo's avatar
    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)
)

     

Lorenzo's avatar
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)
        )
    )
)

Resources