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

     

Lorenzo's avatar
Lorenzo
Silver Contributor
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)), "")
)

 

  • NasTMashups's avatar
    NasTMashups
    Copper Contributor
    Oct 08, 2022
    thank you so much!!!! this is exactly what i wanted!
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Oct 09, 2022
      Glad this helped & Thanks for providing feedback