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?

 

[Peter_SSs]

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

OK, you are set then.

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 ?

This doesn't really matter now but I asked because I had another solution in mind based on the fact that all your sample data had

- numbers (no more than 2 of them) or "-" in A, C & E
- no numbers at all in B & D

 

[Kukorof]

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.

On second though, how will the formula looks like when there are 4 columns of data? wouldn't it be too much repetition in the formula as well? Especially the <>"" and <>"-". Let us say that there is another data column of G2. Wouldn't you have to repeat A2<>"-" more than once in that formula?

 

[Kukorof]

Wait I think get it, All you have to do is nest another AND inside the AND to avoid repeating <>"" and <>"-" for the same cell in the formula. Thanks.

 

[Peter_SSs]

Wait I think get it, All you have to do is nest another AND inside the AND to avoid repeating <>"" and <>"-" for the same cell in the formula. Thanks.

Can you post the formula that you ended up using?