Compare 2 comma delimited cells - non VBA

[ljh89]

Hi All

I've been trying to get a non-VBA solution to comparing 2 comma delimited data cells. This is so that the sheet can be used on excel online.

I have a list of colleagues and each has a set of essential courses and a list of completed courses. Each list is contained in a single cell with each course seperated by a comma. I am looking to extract the courses that are in the essential list but not in the completed list. I've attached an example of the data and highlighted the cells where the issue is.
The most similar cases I have found online is VBA code that gives "additions" to one list compared to another but I can't use these.

Any idea if this is possible using fomulas only and not VBA using Excel 365?

Thanks

Lee

 

[jasonb75]

I think that this should do it

=TEXTJOIN(",",1,IF(ISERROR(SEARCH(MID(","&B2,IF(MID(","&B2,ROW(INDIRECT("1:"&LEN(B2))),1)=",",ROW(INDIRECT("1:"&LEN(B2)))),7),","&C2)),IF(MID(","&B2,ROW(INDIRECT("1:"&LEN(B2))),1)=",",MID(B2,ROW(INDIRECT("1:"&LEN(B2))),5),""),""))

Array confirmed with Ctrl Shift Enter.