Excel 条件格式标识特定行

huangapple go评论66阅读模式
英文:

Excel Conditional Formatting Identify specific rows

问题

我的结果:

Excel 条件格式标识特定行

期望结果:

Excel 条件格式标识特定行

我尝试了一个公式来识别特定的行,规则 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:

Excel 条件格式标识特定行

Expected result:

Excel 条件格式标识特定行

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&lt;&gt;$B1,$D2=$D1),AND($E2=$E3,$B2&lt;&gt;$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

huangapple
  • 本文由 发表于 2023年5月29日 22:24:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/76358148.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定