Compare 2 columns in excel

[cynoo]

Dear experts,

Need help with comparing 2 columns.

Column A must be compared with Column C and the string that's in both the Columns must be the output.

Please see below. Since this is in words/alphabets I'm facing the problems writing it.

Any help is greatly appreciated.

A
xyz-abc-q
xyz-abc-c
xyz-abc-d
xyz-abc-g
xyz-abc-p

C
xyz-mnk-y
xyz-abc-i
xyz-abc-k
xyz-abc-o
xyz-abc-c
xyz-aoc-g
xyz-adc-g
xyz-abc-c
xyz-cbc-n
xyz-abc-q
xyz-abc-l
xyz-abc-b
xyz-abc-n
xyz-abc-m

 

[Blade Hunter]

In D1
=if(countif(A:A,C1)>0,"Duplicate data","")

Drag down, anything with "Duplicate data" next to it is a dupe

 

[cynoo]

In D1
=if(countif(A:A,C1)>0,"Duplicate data","")

Drag down, anything with "Duplicate data" next to it is a dupe

Works like a charm. Thank you so much..

 

[cynoo]

In D1
=if(countif(A:A,C1)>0,"Duplicate data","")

Drag down, anything with "Duplicate data" next to it is a dupe

Is there any way it can be written without dragging down..? I'm comparing columns in my excel sheet which is 10,000+ rows and its getting hung every time I try it.

Appreciate any help..

 

[Blade Hunter]

There would be an array formula that would do it but it is a little out of my scope unfortunately, with any luck one of the array formula genuis's on here will see this and pick it up

Edit: Here you go, Good ol Chip to the rescue

http://www.cpearson.com/excel/Duplicates.aspx