随着时间的推移表单提交 - 比较上次提交的差异-Google表格

发布于 2025-02-09 02:53:41 字数 905 浏览 2 评论 0原文

我有一个通过Google表格中的形式出现的答案列表。请注意，F＆amp列中的答案g被逗号分开

我想最终得到一个像这样的列表，以供名字'测试母亲'...

我合并了所有添加的结果，所有已删除的结果和所有已删除的结果这样的两个列，但是当我尝试寻找差异时，它们都取消了“ lo'”的两个实例，因为它已被删除并再次放回。我需要做的是离开一个实例并取出另一个实例。

我使用了这个公式，

=TEXTJOIN(",",true,unique(ArrayFormula(trim(split(textjoin(", ", true,B2:C2),","))),true,true))

我觉得我可能已经用它掉下了兔子洞，但这是我想到开始比较细胞的唯一方法我的结果正确。

Google表为

原文

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.

The google sheet is here

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

等你爱我 2025-02-16 02:53:41

尝试：

=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)), "¤"), "×", " ")), ",$", ))

更新：

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

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)), "¤"), "×", " ")), ",quot;, ))

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