英文:
If value does not match in a cell then show it in another cell
问题
我有一个Google表格，其中包含多个代码。在一个单元格中可能有多个代码，而在另一个单元格中，用户选择的决策后跟随着代码。
对于给定的代码，响应是固定的。
我希望表格能够在另一列中显示用户选择的代码是否正确或错误（如果正确，则保留单元格为空，否则显示用户选择的错误代码）。
条件如下：
情况1：如果在A列中显示的代码是"P"或"W"，则用户的决策（B列）应该是"NO"或"YES"，而用户选择的代码（C列）应该是"N"（如果决策是"NO"）或"R"（如果决策是"YES"），然后在D列中，如果决策和代码匹配，则保留单元格为空，否则显示用户选择的错误代码。
情况2：如果在A列中显示的代码不包含单元格中的"P"或"W"代码，则用户的决策（B列）应该是"NO"，而用户选择的代码（C列）应该是"IM"，然后在D列中，如果决策和代码匹配，则保留单元格为空，否则显示用户选择的错误代码（注：此条件可以创建在另一列中）。
我附上了一个屏幕截图以供参考。我尝试过各种公式，但都没有成功。
谢谢。
英文:
I have a Google Sheet in which there are multiple codes. In one cell there is more than one code and in the other cell, the decision selected by the user is followed by the code.
Responses are fixed for the given codes.
I want the sheet to show if the codes selected by the users are wrong or correct in another column (if correct then leave the cell blank else to show the code selected by the user i.e the wrong code).
The conditions are:
Case 1: if the code displayed in column A is either "P" or "W" then the user's decision (column B) should be either "NO" or "YES" and the code (column C) selected by the user should be either "N" (if the decision is "NO") or "R" (if the decision is "YES") then in column D if the decision and code matches then leave the cell blank else to show the wrong code selected by the user.
Case 2: if the code displayed in column A does not have "P" or "W" code in the cell then the user's decision (column B) should be "NO" and the code (column C) selected by the user should be "IM" then in column D if the decision and code matches then leave the cell blank else to show the wrong code selected by the user. (P.S.: this condition can be created in another column).
I'm sharing a screenshot for reference. I searched for a formula but nothing worked.
Thank you.
答案1
得分: 1
=indeX(lambda(regX,if(len(A2:A),
IF((regX*(B2:B="NO")(C2:C="N"))+
(regX(B2:B="YES")(C2:C="R"))+
(not(regX)(B2:B="NO")*(C2:C="IM")),,C2:C),))
(regexmatch(A2:A,"\b(P|W)\b)")
英文:
You may try:
=indeX(lambda(regX,if(len(A2:A),
IF((regX*(B2:B="NO")*(C2:C="N"))+
(regX*(B2:B="YES")*(C2:C="R"))+
(not(regX)*(B2:B="NO")*(C2:C="IM")),,C2:C),))
(regexmatch(A2:A,"\b(P|W)\b")))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论