Form submissions over time - compare the difference from last submission - Google Sheets

user_13372148 0
Viewed 39

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

form submissions

I would like to end up with a list looking like this for the Name 'Test Mother'...

expected result

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.

comparing columns

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.

The google sheet is here

google-query-language flatten transpose google-sheets filter
1 Answers

try:

=ARRAYFORMULA(REGEXREPLACE(TRIM(SUBSTITUTE(SPLIT(FLATTEN(QUERY(
 QUERY({A2:A&"¤", IF(F2:F="",,SUBSTITUTE(F2:F, " ", "×")&","), ROW(A2:A)}, 
 "select max(Col2) where not Col1 = '¤' 
  group by Col3 pivot Col1"),,9^9)), "¤"), "×", " ")), ",$", ))

enter image description here

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)), "♦")), ",$", ))

enter image description here

user_56326290
Related