英文:
Excel Conditional Formatting Identify specific rows
问题
我的结果:
期望结果:
我尝试了一个公式来识别特定的行,规则 1:名字(first name 和 last name)相同,源全名(source full name)不同,并包含“NORTH CAROLINA ADMINISTRATIVE OFFICE”,则应突出显示。
规则 2:如果名字(first name 和 last name)相同,但源全名(source full name)不同且不包含“NORTH CAROLINA ADMINISTRATIVE OFFICE”,则不应突出显示。
我尝试的公式是:
=OR(AND($E2=$E1,$B2<>$B1,$D2=$D1),AND($E2=$E3,$B2<>$B3,$D2=$D3))
是否可能有人可以帮忙?
英文:
My Result:
Expected result:
I tried one formula to identify the specific rows the rule 1: first,last name should be same and source full name is different and contains NORTH CAROLINA ADMINISTRATIVE OFFICE
it should be highlighted
Rule 2: if first,last name is same and source full name is is different doesn't contain NORTH CAROLINA ADMINISTRATIVE OFFICE
should not be highlighted
the formula i tried is
=OR(AND($E2=$E1,$B2<>$B1,$D2=$D1),AND($E2=$E3,$B2<>$B3,$D2=$D3))
is that possible can anyone help?
答案1
得分: 1
在示例中,在单元格 J1 中放入以下值:NORTH CAROLINA ADMINISTRATIVE OFFICE
在单元格 G2 中放入以下公式:
=IF(OR(AND(D2=D1,E2=E1,OR(B2=$J$1,B1=$J$1)),AND(D2=D3,E2=E3,OR(B2=$J$1,B3=$J$1))),TRUE,FALSE)
将 G2 下方的所有单元格自动填充。如果在所需的行中获得 TRUE,则使用列 G 中的值进行条件格式设置。
英文:
For this example put in cell J1 the value: NORTH CAROLINA ADMINISTRATIVE OFFICE
put in G2 the formula below:
=IF(OR(AND(D2=D1,E2=E1,OR(B2=$J$1,B1=$J$1)),AND(D2=D3,E2=E3,OR(B2=$J$1,B3=$J$1))),TRUE,FALSE)
Autofill all cells below G2. If you get TRUE in the desired rows, use the value in column G for the conditional formatting
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论