Without VBA…
If you can use a helper column, you can use the MATCH function to test if a value in one column exists in another column (or in another column on another worksheet). It will return an Error if there is no match
To simply identify duplicates, use a helper column
Assume data in Sheet1, Column A, and another list in Sheet2, Column A. In your helper column, row 1, place the following formula:
=If(IsError(Match(A1, 'Sheet2'!A:A,False)),"","Duplicate")
Drag/copy this forumla down, and it should identify the duplicates.
To highlight cells, use conditional formatting:
With some tinkering, you can use this MATCH function in a Conditional Formatting rule which would highlight duplicate values. I would probably do this instead of using a helper column, although the helper column is a great way to “see” results before you make the conditional formatting rule.
Something like:
=NOT(ISERROR(MATCH(A1, 'Sheet2'!A:A,FALSE)))
For Excel 2007 and prior, you cannot use conditional formatting rules that reference other worksheets. In this case, use the helper column and set your formatting rule in column A like:
=B1="Duplicate"
This screenshot is from the 2010 UI, but the same rule should work in 2007/2003 Excel.
