Try this...

List 1 is in the range A1:A1000. Refered to as rng1
List 2 is in the range B1:B1000. Refered to as rng2

Enter this array formula** in D1:

=INDEX(rng1,SMALL(IF(ISNUMBER(MATCH(rng1,rng2,0)), ROW(rng1)),ROWS(D$1:D1))-MIN(ROW(rng1))+1)

Copy down until you #NUM! errors meaning all the dupes have been extracted.

Or, this array formula** is a a few keystrokes shorter and will list the
*numbers* in ascending order:

=SMALL(IF(ISNUMBER(MATCH(rng1,rng2,0)),rng1),ROWS( D$1:D1))

Copy down until you #NUM! errors meaning all the dupes have been extracted.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Charlotte B" <Charlotte wrote in message
...
I have two columns consisting of 1000 cells that each have 7 digits. For
example

8956743 8658237
8967842 8753689
8974389 8896536

These two columns share some of the same numbers. I want to find and LIST
all of these shared numbers. Some of these numbers are the same and some
are
different. I just need a formula to find the duplicates. HELP ME!!