使用无组织数据跨多个列匹配重复的Excel

I got two lists each containing the following parameters: Serial number, name of product and name of the owner.

我有两个列表，每个列表包含以下参数:序列号、产品名称和所有者名称。

I wish to be able to compare the two lists to be able to mark duplicates where a duplicate must have all three categories identical. One list usually has more lines of items and the lists are not organized.

我希望能够比较这两个列表，以便能够标记重复项，在重复项必须具有所有三个类别相同的地方。一个列表通常有更多的项目行，而列表没有组织。

Usually I would use this formula:

通常我会用这个公式:

=IF(COUNTIF($B:$B,$A:$A)=0, " ", " V ")

=IF(COUNTIF($B:$B，$A:$A)=0， " " "， " V ")

to check unorganized values across one parameter but I am not sure how to do this for all three parameters. Can anyone suggest a solution?

要跨一个参数检查无组织的值，但我不确定如何对所有三个参数执行此操作。有人能提出解决方案吗?

1 个解决方案

#1

With sample data like this:

样本数据如下:

The formula is:

这个公式是:

=IFERROR(INDEX(MATCH(E2&F2&G2,$A$2:$A$8&$B$2:$B$8&$C$2:$C$8,0),1)>0,FALSE)

Which:

哪一个:

takes the combo of the row of the list you are checking E2&F2&G2
获取正在检查e2 & f2和g2的列表的行的组合
tries to MATCH it against all the rows of the other list $A$2:$A$8&$B$2:$B$8&$C$2:$C$8
试着将它与其他列表的所有行匹配$2:$A$8&$ 2:$B$8&$ 2:$B$8&$ 2:$C$8
returns TRUE for a match - using INDEX to pull out the result
返回真值，用于使用索引提取结果
returns FALSE for an error in the MATCH (#VALUE! error)
为匹配中的错误返回FALSE(#值!错误)

#1