Try this...

Data in the range A2:B9.

Enter this formula in D2. This will return the count of matches and be used
as an error trap.

=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B9,A2:A9,0))))

Enter this array formula** in E2 and copy down to E9.

=IF(ROWS(E$2:E2)D$2,"",INDEX(B:B,SMALL(IF(ISNUMBE R(MATCH(B$2:B$9,A$2:A$9,0)),ROW(B$2:B$9)),ROWS(E$2 :E2))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Dingy101" wrote in message
...
First two columns of data which will vary depending on other functions in
worksheet

8 9 5
5 7 5
5 7 4
4 5 0
2 5
1 5
0 4
0 0

I want to in a third column to list any occurences of values in column 2
that appear in column 1, including multiples, there will always be 16 rows
of
this data. I already have data sorted in accending order in first and
second
columns using Large function. Also would be helpful to blank any 0 value.

Would be nice if empty values in third column are blank, but can deal with
anything.

Thank You in advance

Gary