英文:
How do I highlight incorrectly matching keywords in a two column list that should match in Google sheets?
问题
我感觉这个很复杂，所以如果需要的话，请提出澄清的问题。我有一个关键词列表，以及下一列中的作者列表。基于另外两个列表，我需要突出显示任何与关键词不匹配的作者名称。请查看下面的屏幕截图，了解当前的电子表格和期望的输出。我最终要处理的实际关键词和作者列表将比这长得多，大约有13,000行。这里是一个测试表格: https://docs.google.com/spreadsheets/d/1_JppmT-KoUGIadm3uvgYaoGhRsnTn10kShRD6Ia3Dpk/edit#gid=0
英文:
I feel like this is complicated to explain so please ask clarifying questions if you need to. I have a list of keywords, with a list of authors in the next column. Based on two other lists, I need to highlight any author names that do not match the keyword. Please see screenshots below for the current spreadsheet and the desired output. The actual keyword and author list I will end up working with will be much longer than this, approximately 13,000 lines. Here is a testing sheet: https://docs.google.com/spreadsheets/d/1_JppmT-KoUGIadm3uvgYaoGhRsnTn10kShRD6Ia3Dpk/edit#gid=0
答案1
得分: 2
以下是翻译好的内容：
For those additional columns, you can use this formula that nests different REDUCE functions:
对于那些额外的列，您可以使用此嵌套不同REDUCE函数的公式：
=QUERY(REDUCE({"关键词","作者"},FILTER(A2:A,A2:A<>""),LAMBDA(a,b,{a;REDUCE({"",""},FILTER(E2:E,D2:D=b),LAMBDA(c,d,{c;REDUCE({"",""},FILTER(H2:H,G2:G=d),LAMBDA(e,f,{e;b,f}))}) )}), "WHERE Col1 is not null")
For conditional formatting, it checks with MATCH if it is found inside the filtered values of K column according with J column:
对于条件格式化，它使用MATCH检查是否在K列的过滤值中根据J列找到：
=ISNA(MATCH(B2,FILTER(K:K,J:J=A2),0))*(B2<>"")
英文:
For those additional columns, you can use this formula that nests different REDUCE functions:
=QUERY(REDUCE({"Keyword","Author"},FILTER(A2:A,A2:A<>""),LAMBDA(a,b,{a;REDUCE({"",""},FILTER(E2:E,D2:D=b),LAMBDA(c,d,{c;REDUCE({"",""},FILTER(H2:H,G2:G=d),LAMBDA(e,f,{e;b,f}))}) )}) ),"WHERE Col1 is not null")
For conditional formatting, it checks with MATCH if it is found inside the filtered values of K column according with J column:
=ISNA(MATCH(B2,FILTER(K:K,J:J=A2),0))*(B2<>"")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论