Match, search or find function

[bt123123]

I don't know which formula to use...

in below case, I want to formula pick up CNYAUD (in A5) is a duplication because we have AUDCNY (in A2).

Many Thanks

excel fx1.JPG

[WideBoyDixon]

I'm assuming these are currencies. Try this in B2:

Please Login or Register  to view this content.

Then copy down.

WBD

[Phuocam]

Try this in B3:

=IF(COUNTIF($A$2:A2,RIGHT(A3,3)&LEFT(A3,3))+COUNTIF($A$2:A2,A3)>0,"Duplicate","")

[bt123123]

thanks. it works