Getting Duplicates identified, return value "duplicate" across multiple columns.

[Viau1337]

I'm presently working on a sheet where i want to test value of left cell against a range of data also found on the left side of it and mark it as duplicate if it is found within that range.

I managed to get it working for the first column, however when i try to apply again in a new column, it marks everything as a duplicate.

The formula I'm using in F column is =IF(COUNTIF($E$2:$E2, $E2)>1, "Duplicate", "")

Then in column g i run something that will only run if cell on immediate left is not blank.

Then, i want to run my formula again, in h, to test if there are duplicates again.

Formula used is
=IF(COUNTIF($E$2:$G2, $G2)>1, "Duplicate", "")

However, said formula returns false duplicates , I think its seeing the empty fields as duplicates?
Also i noticed that it doesn't mark duplicates either or only some of them..

Any idea?

Cheers

 

[Z51]

It's not the empty fields, I just don't think COUNTIF works that way (across multiple columns). You can try creating a helper column to concatenate all of the cells in each row and then use a COUNTIF against that helper value within that column, or perhaps something like this SUMPRODUCT formula:

=IF(SUMPRODUCT(($E$2:$E2=E2)*($F$2:$F2=F2)*($G$2:$G2=G2))>1,"Duplicate","")

 

[Viau1337]

Thank you! That did the trick