We help IT Professionals succeed at work.

compare data in 2 different excel 2010 worksheets

contrain
contrain asked
on
In XL 2010, I have 2 worksheets, one has thousands of rows of data (Sheet A) and the other has hundreds of rows of data (Sheet B). Some of the data in sheet B is in Sheet A. How can I find out which data in sheet B is also in Sheet A?

Thanks,
Comment
Watch Question

I suggest VLOOKUP.  Say what you're looking for is in column A of both SheetA and SheetB.

In column B of SheetB, enter =IFERROR(VLOOKUP(A1,'SheetA'!$A$1:$A10000$,1,FALSE),"")

Drag down to all rows in Sheet B.  This will reveal the data if it's a duplicate, and blanks if the data is not found in SheetA.

Author

Commented:
Hi Katie,
When I paste that function in an xl sheet on sheet b cell B1, it just puts in the text of the function, it doesn't return any results. I also noticed that in your range referring to Sheet A, it says $A10000$, should there be a $ at the end of the range reference?

Thanks,
Sorry, that should read, "$A$10000" (or whatever range you want).

To correct what appears in the cell, check the formatting and make sure it's not Text formatting.

Author

Commented:
Except for the tupo which the Expert corrected in a later email, this solution worked perfectly. I was able to paste the correct function in a cell and it returned what I was looking for.