[excel] I want to compare two lists in different worksheets in Excel to locate any duplicates

I know this is very simple but I still need help:

I have a list of properties that have finished a training. I need the names of the ones that have not done this training, but the system does not give me such a list. However, I have a list of all our properties. That means I have to compare two columns in different worksheets.

The properties are identified by ID Numbers. What would make my life easier is that if there is a formula that can detect duplicates and highlight on the complete list of hotels so the ones that are not highlighted have not done the training.

Let me know if you have any recommendations for me!

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.