I have two diferent lists of part numbers. I wish to identify the numbers
that are duplicated within both.

Hi,

The first list is in A2:B19, and this range is named OldList. The second
list is in D2:E19, and the range is named NewList. The ranges were named
using the Insert - Name - Define command. Naming the ranges is not necessary,
but it makes them easier to work with.

As you can see, items in OldList that do not appear in NewList are
highlighted with a yellow background. Items in NewList that do not appear in
OldList are highlighted with a green background. These colors are the result
of Conditional Formatting.

How to do it
Start by selecting the OldList range.
Choose Format - Conditional Formatting
In the Conditional Formatting dialog box, use the drop-down list to choose
Formula is.
Enter this formula:

=COUNTIF(NewList,A2)=0
Click the Format button and specify the formatting to apply when the
condition is true (a yellow background in this example).
Click OK

The cells in the NewList range will use a similar conditional formatting
formula.

Select the NewList range.
Choose Format - Conditional Formatting
In the Conditional Formatting dialog box, use the drop-down list to choose
Formula is.
Enter this formula:

=COUNTIF(OldList,D2)=0
Click the Format button and specify the formatting to apply when the
condition is true (a green background in this example).
Click OK
Both of these conditional formatting formulas use the COUNTIF function. This
function counts the number of times a particular value appears in a range. If
the formula returns 0, it means that the item does not appear in the range.
Therefore, the conditional formatting kicks in and the cell's background
color is changed.

The cell reference in the COUNTIF function should always be the upper left
cell of the selected range.

hth
regards from Brazil
Marcelo

"SPURLING" escreveu:

I have two diferent lists of part numbers. I wish to identify the numbers
that are duplicated within both.

Try using Countif.

This will count any occurances of items in current sheet within Sheet2
column A. If anything other than 0 appears, then you have duplicates.

You can change sheetnames and ranges/references to suit.

=Countif(Sheet2!$A$1:$A$100,A1) copied down


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=555994