Finding duplicates (horizontally) with formula instead of conditional formattng

[Kukorof]

Suppose I have data in A2, C2, and E2. And in column F2 i want to show that if any of those three separate cells (it is not a range) have duplicate data horizontally (in a row), then show in F2 "duplicate" if not then show "safe".

How would I do that efficiently without repetitive formula or multiple nested IF functions?

 

[Kukorof]

Sorry Forgot to mention that in this case the context given is that A2, C2 and E2 must have data and none of them is blank or "-".

 

[Marcelo Branco]

Maybe...

=IF(OR(A2=C2,A2=E2,C2=E2),"Duplicate","Safe")

M.

 

[Kukorof]

Maybe...

=IF(OR(A2=C2,A2=E2,C2=E2),"Duplicate","Safe")

M.

And what if suppose I wanted to change the condition that the A2 contains data, C2="-", and E2="-", with what formula can I use to verify that the data in A2 is not duplicated in C2 or E2, while ignoring the fact that there is a duplicate of E2=C2="-" ? With what formula could i do that?

 

[Marcelo Branco]

Not sure i understand what you need. See if this works

=IF(SUMPRODUCT(--(COUNTIF(A2:E2,A2:E2)*(MOD(COLUMN(A2:E2)-COLUMN(A2)+1,2)=1)*(A2:E2<>"-")>1)),"Duplicate","Safe")

M.

 

[Marcelo Branco]

hmm...my formula in post 5 has a flaw

Try this new version
=IF(OR(AND(A2<>"",A2<>"-",A2=C2),AND(A2<>"",A2<>"-",A2=E2),AND(C2<>"",C2<>"-",C2=E2)),"Duplicate","Safe")

M.

 

[Kukorof]

Not sure i understand what you need. See if this works

=IF(SUMPRODUCT(--(COUNTIF(A2:E2,A2:E2)*(MOD(COLUMN(A2:E2)-COLUMN(A2)+1,2)=1)*(A2:E2<>"-")>1)),"Duplicate","Safe")

M.

Let me clarify then, supposed i have the following table:

A B C D E F G 1 Data1 Y/N Data2 Y/N Data3 Y/N Duplicate/Safe 2 121 Y - Y - N 3 - Y 133 N 133 N 4 - N - Y - N 5 - Y 565 N - Y 6 - Y - N 336 Y 7 778 Y 778 Y - N <colgroup><col span="7"><col></colgroup><tbody> </tbody>

<colgroup><col span="7"><col></colgroup><tbody>
</tbody>

Now I want only cell G3 and G7 to indicate "Duplicate" the rest in column G should indicate "Safe". With what formula could I do that?

Regards

 

[Kukorof]

Let me clarify then, supposed i have the following table:

A B C D E F G 1 Data1 Y/N Data2 Y/N Data3 Y/N Duplicate/Safe 2 121 Y - Y - N 3 - Y 133 N 133 N 4 - N - Y - N 5 - Y 565 N - Y 6 - Y - N 336 Y 7 778 Y 778 Y - N <tbody> </tbody>

<tbody>
</tbody>

Now I want only cell G3 and G7 to indicate "Duplicate" the rest in column G should indicate "Safe". With what formula could I do that?

Regards

The formula should be able to exclude the fact that in each row "-" is duplicate and should not include cell of column B, D and F (it should not use a range) in the formula.

 

[Peter_SSs]

Doesn't Marcelo's post 6 formula meet the requirement?

Can any of your rows have 3 numbers in the relevant columns?

 

[Kukorof]

Doesn't Marcelo's post 6 formula meet the requirement?

Can any of your rows have 3 numbers in the relevant columns?

I didn't notice post #6 sorry. Yes it did solved my problem.

Yes it can have 3 or more or less than that and it doesn't have to be numbers, would you explain why did you ask ?