Need to compare two columns of data for duplicates

[Sandie]

I have 2 worksheets with rows of data. I need to compare the data in column
1, worksheet 1, to the data in column 1, worksheet. I need to find out if
there is any of the data in column 1, worksheet 1 that is duplicate to column
1, worksheet 2, but I need the duplicate information to appear in yet another
column.
Do I need to start a new worksheet with both columns from worksheet 1 and
worksheet 2? Does anyone have a formula that I can use to get this
information??

 

[Biff]

Hi Sandie!

Try this:

=INDEX(Sheet2!A$1:A$10,SMALL(IF(ISNUMBER(MATCH(Sheet2!
A$1:A$10,Sheet1!A$1:A$10,0)),ROW(A$1:A$10)),ROW(1:1)))

Entered as an array - CTRL,SHIFT,ENTER

Copy down until you get #NUM! errors.

This will return "data" that is common (duplicated) to
both sheet1 col A and sheet2 col A.

Biff

 

[Sandie]

Thanks Biff:
What formula would I use to compare two columns in the SAME worksheet, but I
want to extract the differences. For Example, I want to compare Column A, to
Column B and have the differences input into column C.

 

[Max]

... I want to compare Column A, to
Column B and have the differences input into column C.

Assume range is row1 to row100
Try in C1: =IF(COUNTIF($A$1:$A$100,B1)=0,B1,"")
Copy down

 

[Sandie]

thank you!!

Assume range is row1 to row100
Try in C1: =IF(COUNTIF($A$1:$A$100,B1)=0,B1,"")
Copy down

 

[Max]

thank you!!

Pleasure` Sandie !
Thanks for the feedback