Excel array formula to find duplicate row across multiple columns

Question

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

Data:

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

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

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...

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...

Thanks!

Answer 1

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.

Formula in cell D1:

=CONCATENATE(A1,B1,C1)

Formula in cell E1:

=COUNTIF( D$1:D1, D1 )

Formula in cell F1:

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

--Edit:

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

Formula in cell D1:

=CONCATENATE(A1,B1,C1)

Formula in cell E1:

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

Formula in cell F1:

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

Cheers!