Excel数组公式可在多列中查找重复的行 [英] Excel array formula to find duplicate row across multiple columns

问题描述

有没有一种方法可以使用数组公式在多列中指示重复的行?

is there a way to indicate duplicate rows across multiple columns using an array formula?

数据:

AA1   BB1   CC2   duplicate
AA1   BB2   CC1
AA1   BB1   CC2   duplicate
AA1   BB1   CC1

在上表中，我需要通过在第4列中添加重复"来指示第1行和第3行.

In the above table, rows 1 and 3 are the ones I need to indicate, by putting "duplicate" in column 4.

我知道Excel中的删除重复项功能，但是在实际删除重复行之前，我必须先查看重复行.另外，由于在此过程中文件进一步发生了什么情况，因此无法添加隐藏的帮助器列...

I know of the remove duplicates functionality in Excel, but I have to see the duplicate lines before actually deleting them. Also, adding a hidden helper column is not an option because of what happens with the file further down in the process...

如果数据仅在一列中，则countif公式将起作用.所以我希望某种countif(col1 & col2 & col3, range(A:A & B:B & C;C))可以解决问题...

If data was just in one column, a countif formula would work. So I was hoping some sort of countif(col1 & col2 & col3, range(A:A & B:B & C;C)) could do the trick...

谢谢！

推荐答案

您必须了解重复的含义.这意味着是否还会再出现原始值.在您的示例中，第一行不是重复的，因为它之前没有任何出现.下一个值是重复的，因为它是第二次出现.我为您准备了一种提取重复项并将其标记为需要的方法.

You have to understand what does a duplicate mean. It means if there is occurrence of any more occurrences of the original value. In you example, the first row is NOT a duplicate because it does not have any occurrences before. The next value is a duplicate because it has a second occurrence. I have prepared for you a method to extract out duplicates and mark them as need.

D1单元格中的公式:

Formula in cell D1:

=CONCATENATE(A1,B1,C1)

单元格E1中的公式:

=COUNTIF( D$1:D1, D1 )

单元格F1中的公式:

=IF(E1>1,"Duplicate","")

-

如果要显示所有重复项(包括原始值)

If you want to show all duplicates(including the original value)

D1单元格中的公式:

Formula in cell D1:

=CONCATENATE(A1,B1,C1)

单元格E1中的公式:

=IF(COUNTIF($D$1:$D$4,D1)=1,0,1)

单元格F1中的公式:

=IF(E1>0,"Duplicate","")

干杯！

这篇关于Excel数组公式可在多列中查找重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！