Excel formula

Question

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, other courses might be affected. I would like to set a formula for column F in sheet Postgraduate to reflect the different courses that shares the same topic. How do I do that? My database is quite large, 1 programme might have 12 courses x 10+ topics and I have at least 10+ different programmes. I would need to figure out how to formulate the column shared course to show the below eg without having to do it manually.&nbsp;Column A - Course Name &nbsp; &nbsp; &nbsp;Column B - Topic Code&nbsp; &nbsp;Shared CourseABC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ABC,CCCABC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 233&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ABC,BBCABC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 426&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; No shared courseBBC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 561BBC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 233BBC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 122CCC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 122CCC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 333CCC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;123&nbsp;

m_tarler · Answer

To get what you show you can use:=TEXTJOIN(",",,FILTER(A2&amp;","&amp;$A$2:$A$10,($B$2:$B$10=B2)*($A$2:$A$10&lt;&gt;A2),"No shared course"))and fill downIf 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&lt;&gt;A2),"No shared course"))and fill downIf 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)&amp;","&amp;names,(codes=INDEX(codes,q))*(names&lt;&gt;INDEX(names,q)),"No shared course"))))),1))&nbsp;

Forum Discussion

Excel formula

1 Reply

Resources