Forum Discussion
Excel formula
I have many different topics in many different courses in a college. some of these topics may be shared across the different courses. if there is a change in any of these share topics in one course, ...
To get what you show you can use:
=TEXTJOIN(",",,FILTER(A2&","&$A$2:$A$10,($B$2:$B$10=B2)*($A$2:$A$10<>A2),"No shared course"))and fill down
If you only want to see the "other" courses then you can use:
=TEXTJOIN(",",,FILTER($A$2:$A$10,($B$2:$B$10=B2)*($A$2:$A$10<>A2),"No shared course"))and fill down
If you don't want to fill down you can use REDUCE-LAMBDA to VSTACK them all together
=LET(names, A2:A10, codes, B2:B10,
DROP(REDUCE("",SEQUENCE(ROWS(names)),LAMBDA(p,q,VSTACK(p,TEXTJOIN(",",,FILTER(INDEX(names,q)&","&names,(codes=INDEX(codes,q))*(names<>INDEX(names,q)),"No shared course"))))),1))