Find the words which are not common between Group 1 and Group 2.

Numerous Methods to Accomplish a Task:

Task:

Find the words which are not common between Group 1 and Group 2.

Solution:

Excel:

=BYROW(A2:B8,LAMBDA(x,ARRAYTOTEXT(IFERROR(UNIQUE(TEXTSPLIT(ARRAYTOTEXT(x),,", ",1),,1),""))))

Power Query:

Method-1:

let

 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 Result = Table.FromRecords(Table.TransformRows(Source, each _ &

 [

 L = Text.Split(Text.Combine(Record.ToList(_), ", "), ", "),

 Result = Text.Combine(List.Select(L, (f) => List.Count( List.PositionOf(L,f,2)) = 1), ", ")

 ][[Result]]))

in

 Result

Method-2:

let

 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 Result = Table.CombineColumns(Source, Table.ColumnNames(Source), each

 [

 L = List.Transform(_, (f) => try Text.Split(f ,", ") otherwise {null} ),

 fin = Text.Combine(List.Difference( List.Distinct(List.Combine(L)), List.Intersect(L)),", ")

 ][fin] , "Result")

in

 Result

Method-3:

Suggested by Abdallah Ally

let

 Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],

 f = (t) => try Text.Split(t, ", ") otherwise {},

 Result = Table.AddColumn(

 Source,

 "Answer",

 each Text.Combine(

 List.Difference(f([Group 1]), f([Group 2])) & List.Difference(f([Group 2]), f([Group 1])),

 ", "

 )

 )

in

 Result

Python in Excel:

from collections import Counter

df = xl("A1:B8", 1)

def exp(x):

  if x is None:

  return []

 return str(x).split(", ")

def extract_uncommon(row):

  g1, g2 = exp(row["Group 1"]), exp(row["Group 2"])

  L = g1 + g2

  C = [i for i,c in Counter(L).items() if c ==1 ]

  return ", ".join(C)

df["Result"] = df.apply(extract_uncommon, axis = 1)

df