I have a list of answers that comes in through a form in Google sheets like this. Please note that the answers in Column F & G are comma seperated
I would like to end up with a list looking like this for the Name 'Test Mother'...
I merged all my added results and all my deleted results in two columns like this but when I try to look for the difference they take out both the instances of 'lo lo' because it has been deleted and put back again. What I need it to do is to leave one instance and take out the other.
I used this formula
=TEXTJOIN(",",true,unique(ArrayFormula(trim(split(textjoin(", ", true,B2:C2),","))),true,true))
I feel I might have gone down the rabbit hole with this one but it was the only way I could think of starting comparing the cells but as it takes our all the instances it doesn't give me the right result in the end.
update:
=ARRAYFORMULA(REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(QUERY((FILTER(TEXT(
QUERY(TRIM(SPLIT(FLATTEN(A2:A9&"×"&SPLIT(F2:F9, ",")), "×")),
"where Col2 is not null", ), {"@♦", "@,"}), NOT(COUNTIF(FLATTEN(QUERY(TRANSPOSE(TEXT(
QUERY(TRIM(SPLIT(FLATTEN(A2:A9&"×"&SPLIT(G2:G9, ",")), "×")),
"where Col2 is not null", ), {"@♦", "@,"})),,9^9))&COUNTIFS(FLATTEN(QUERY(TRANSPOSE(TEXT(
QUERY(TRIM(SPLIT(FLATTEN(A2:A9&"×"&SPLIT(G2:G9, ",")), "×")),
"where Col2 is not null", ), {"@♦", "@,"})),,9^9)), FLATTEN(QUERY(TRANSPOSE(TEXT(
QUERY(TRIM(SPLIT(FLATTEN(A2:A9&"×"&SPLIT(G2:G9, ",")), "×")),
"where Col2 is not null", ), {"@♦", "@,"})),,9^9)), SEQUENCE(ROWS(TEXT(
QUERY(TRIM(SPLIT(FLATTEN(A2:A9&"×"&SPLIT(G2:G9, ",")), "×")),
"where Col2 is not null", ), {"@♦", "@,"}))), "<="&SEQUENCE(ROWS(TEXT(
QUERY(TRIM(SPLIT(FLATTEN(A2:A9&"×"&SPLIT(G2:G9, ",")), "×")),
"where Col2 is not null", ), {"@♦", "@,"})))), FLATTEN(QUERY(TRANSPOSE(TEXT(
QUERY(TRIM(SPLIT(FLATTEN(A2:A9&"×"&SPLIT(F2:F9, ",")), "×")),
"where Col2 is not null", ), {"@♦", "@,"})),,9^9))&COUNTIFS(FLATTEN(QUERY(TRANSPOSE(TEXT(
QUERY(TRIM(SPLIT(FLATTEN(A2:A9&"×"&SPLIT(F2:F9, ",")), "×")),
"where Col2 is not null", ), {"@♦", "@,"})),,9^9)), FLATTEN(QUERY(TRANSPOSE(TEXT(
QUERY(TRIM(SPLIT(FLATTEN(A2:A9&"×"&SPLIT(F2:F9, ",")), "×")),
"where Col2 is not null", ), {"@♦", "@,"})),,9^9)), SEQUENCE(ROWS(TEXT(
QUERY(TRIM(SPLIT(FLATTEN(A2:A9&"×"&SPLIT(F2:F9, ",")), "×")),
"where Col2 is not null", ), {"@♦", "@,"}))), "<="&SEQUENCE(ROWS(TEXT(
QUERY(TRIM(SPLIT(FLATTEN(A2:A9&"×"&SPLIT(F2:F9, ",")), "×")),
"where Col2 is not null", ), {"@♦", "@,"})))))))),
"select max(Col2) group by Col2 pivot Col1"),,9^9)), "♦")), ",$", ))