How to compare lists within two fields

[averyoldakowski]

Hello, 

I have a dataset with two fields that I am trying to compare which can have a practically unlimited number of items in the list. I'd like to compare the items in the list and output both the similarities and differences into separate columns. My data currently looks like these first two columns:

List1	List2	Similarities	Differences
A,B,C,D,E	B,D,C,E,F	B,C,D,E	A,F
A,B,C,D	E,F,G	[NULL]	A,B,C,D,E,F,G
A,B,C,D,E	B,C,E,D,A	A,B,C,D,E	[NULL]

I realize I could do something with text to columns but I believe that wouldn't be the most efficient because the number of items in the list varies drastically.

Thank you!

Avery

[averyoldakowski]

Also, it is possible that there are duplicates within either list: 

List1	List2	Similarities	Differences
A,B,C,D,A,B,C,D	A,B	A,B	C,D

[Qiu · Accepted solution]

@averyoldakowski 
I expect your items are seperated withe delimeter "," then we can break them into each of them. 
then it is all about Join and Union. 😁

[averyoldakowski]

Perfect, thank you!!