Forum Discussion

NasTMashups's avatar
NasTMashups
Copper Contributor
Oct 07, 2022
Solved

Unique spilling behavior

Hello there. i have a document where i have many columns with only 3 i care about: groups, students, and scores. there are 9 groups with more that could be added, and each one has around 20 unique st...
excel
Formulas and Functions
office 365
  • Lorenzo's avatar
    Lorenzo
    Oct 07, 2022

    Hi NasTMashups 

     

    Assuming I understood...

     

    in F2:

    =LET(
    uGroup, UNIQUE(B3:B13),
    cols,   SEQUENCE(,COUNTA(uGroup)*2),
    IF(ISODD(cols), INDEX(uGroup,ROUNDUP(cols/2,0)), "")
)

     

mtarler's avatar
mtarler
Silver Contributor
Oct 07, 2022
since you have UNIQUE you should also have TOCOL() which should do what you want, I think. basically take 2 columns of info and merge it into a single column and you can select byrow or bycol (you want byrow).
Can you post your sheet and formula?
Lorenzo's avatar
Lorenzo
Silver Contributor
Oct 09, 2022
mtarler. Not sure I understand your idea 😞