与主列表- Excel相比，突出显示缺少的唯一对

Question

在表1中，我有一个包含相应属性的主数据列表。有些数据有多个属性，有些数据只有一个属性，并且有可能出现空白。属性在分配给不同名称时可以重复。

我在下面发布了一些示例数据，这样我们就可以讨论相同的单元格/名称等等。

在第二页中，有更多自由格式的数据输入/分析正在进行。用户可以使用数据验证从下拉菜单中选择“name”，然后可以使用下拉菜单从与该名称相对应的可用属性中选择。名称和属性可以以任何顺序出现在第2页上。

在第二个工作表中考虑所有配对是很重要的。

是否可以使用条件格式来高亮显示“Name”字段(在第2页上)，直到至少有一行存在所有可能的配对？在下面的例子中，你可以看到，我们忘记了放任何有关萨莉快乐的信息，因此“萨利”被突出显示，以提请人们注意有一些缺失的信息。

​

​

当前想法:我已经有了一个与相应名称相匹配的属性列表--这就是驱动第2页下拉菜单的原因，并且在第2页上选择名称时在后台工作表中生成。我可以计算此范围内的非空白单元格，以找出所需的对的总数。

然后，我想计算与当前行具有相同名称的行上的非重复属性的数量，并比较此值。

我希望这能进入数组公式的范围，但可能是错的.我还期望数组公式不直接处理条件格式，并要求使用“助手列”来驱动格式设置。如果这是不对的，请告诉我？

下面的内容(为可读性而格式化为伪代码，但这应该作为高级描述来读取，而不是实际的代码)。

{Count the 1s in the array(AND(
'Check if it's a name match'
If($D$1:$D$10=[$ACurrent],[set flag to 1],[set flag to 0])
'Check if it's a unique value'
[somehow check array values set at 1 to see if there is a duplicate value in column E, and then set the array value to zero if so])
}

这种方法有意义吗?我将如何构建这个实际的公式？

如果需要，我不介意使用VBA，但是如果可能的话，我更愿意避免使用VBA(公司政策，对不起)。

Answer 1

如果Sally, Happy值未出现在Sheet2中，则需要高亮显示该值，因此只需要一个COUNTIFS

COUNTIFS函数

我有这个：

​

​

我使用了一个基于我自己公式的条件格式规则。我所用的公式是：

=IF($B2="";IF(COUNTIFS($F$2:$F$12;$A2;$G$2:$G$12;"")=0;TRUE;FALSE);IF(COUNTIFS($F$2:$F$12;$A2;$G$2:$G$12;$B2)=0;TRUE;FALSE))

有两个COUNTIFS，因为空白值是可能的：

1. IF(COUNTIFS($F$2:$F$12;$A2;$G$2:$G$12;"")=0;TRUE;FALSE)将计算Sheet2中有多少行具有相同的名称和非空白属性。如果为0，则返回True并突出显示行。
2. IF(COUNTIFS($F$2:$F$12;$A2;$G$2:$G$12;$B2)=0;TRUE;FALSE)将计算有多少行具有相同的名称和空白属性。如果为0，则返回True并突出显示

使用初始值IF可以空白或非空白属性，只是为了确保计算值的所有可能性。

我已经上传了一个示例到mi Gdrive，以防您想下载并在您的PC上获得自动翻译的公式。

https://drive.google.com/open?id=1Im4LoaK4EIvINBj7tfyEcYk9juWUp7zr

希望你能适应你的需要。

Answer 2

@Foxfire、Burns和Burns帮助我解决了我的问题--我稍微修改了他的公式，删除了双COUNTIFS，并考虑了主数据中两个单元格为空的行--下面就是我所使用的。

=IF (OR($A2<>"",$B2<>""),
        (IF(COUNTIFS($D$2:$D$12,$A2,$E$2:$E$12,IF($B2="","",$B2))=0,TRUE,FALSE)
        )
    )

这是通过从以下逻辑检查返回TRUE或FALSE来实现的--如果最终结果是TRUE，则会出现格式设置。

1. OR($A2<>"",$B2<>"") -检查中的任何一个单元格在主单元中都不是空白的.-如果两个单元格为空，则返回FALSE，并且不格式化任何内容(请参阅A10:B10)。
2. IF(COUNTIFS($D$2:$D$12,$A2,$E$2:$E$12,[see point 3])<1,TRUE,FALSE) --这将计算范围$D$2:$D$12包含单元格$A2值和range $E$2:$E$12包含单元格$B2值的次数。如果该数字为<1 (零匹配)，则该语句返回TRUE并实现格式设置。
3. IF($B2="","",$B2) -此嵌套IF语句检查单元格$B2是否为空，如果为空则返回""，否则保留$B2的值。这是因为COUNTIFS将在空白时将单元格视为‘零’，否则(请参见A9:B9)。
4. 我没有使用类似的嵌套if来检查$A2是否为空，因为它不应该是空的，所以在这里突出显示用户在数据输入中也犯了错误是很有用的(请参阅A2:B2 --一旦用户输入名称，这将保持红色，因为它将不再与D2:E2匹配)。

参考图像如下：

​

​