Forum Discussion
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 students
i have 2 formulas currently set up. one is a UNIQUE function which lists all unique groups horizontally, and another formula that selects 10 random students along with their scores from each group and puts them under their respective groups extracted by the first formula. the problem i'm facing is that under each group i need two columns: student and score, and the UNIQUE function that lists the groups does not support merged cells. Does anyone have any alternative methods i can use to get a solution? or is there a way to make the UNIQUE function insert a blank call after each entry it adds?
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)), "") )
6 Replies
- 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? If you already have the latest 365 Text and Array functions, still with your Groups in B3:B13:
=TEXTSPLIT(TEXTJOIN(" ",,UNIQUE(B3:B13)) & " "," ")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)), "") )- thank you so much!!!! this is exactly what i wanted!
- Glad this helped & Thanks for providing feedback
