I'm not sure whether you want to check for a duplicate on the corresponding row in Sheet A or whether any cell in Column A on Sheet A has the value in the corresponding row in Sheet B. I'm assuming your first sheet is named
Sheet1, the second sheet is named
Sheet2, and both data sets start on the first row of their sheet.
Testing for duplicates in the entire column
=IF(COUNTIF(Sheet1!A:A,B1)>0,"YES","NO") in A1 on Sheet2. It will say "YES" or "NO" depending on whether any cell in column A on Sheet1 has the value in B1 on Sheet2. Drag the cell down to adjust it for more rows.
How it works:
COUNTIF returns the number of cells in a range that satisfy a condition. The range
Sheet1!A:A includes all cells in column A of Sheet1. They are tested for equality with B1 (which will be adjusted when you drag the cell). The
IF function returns a value depending on the value of its condition, which is whether the number of cells from
COUNTIF is nonzero.
Highlighting duplicates from the entire column
Added in response to a comment.
To highlight based on the presence of duplicates, conditional formatting needs to be applied. Click on the B column header in the second sheet to select the entire column, then choose Manage Rules under the Conditional Formatting button. Add a new rule with the type "Use a formula to determine which cells to format." Press the Format button to select what formatting will be applied. (Highlights can be done with the Fill tab.) Place
=(COUNTIF(Sheet1!A:A,B1)>0) in the formula box, then click OK.
Testing for duplicates in the corresponding row
=IF(Sheet1!A1=B1,"YES","NO") in A1 on Sheet2. Again, it says "YES" or "NO" and you can drag it down to more rows.
How it works: the
Sheet1!A1 (which refers to A1 on Sheet1) and
B1 tests whether those cells' values are equal.
IF functions the same as above.