What I would expect to see in fourth column is only a single 270 entry,
rest
looks good.

From this comment I see I might have confused you.

Fourth column should have:
280
275
275
270
0
0
0
0

I meant that it should have only one 270 entry, not the four it shows. The
280 and 275 pair are fine.

Gary






"T. Valko" wrote:

It almost works, if there is such a thing.

A formula either works or it doesn't! There is no gray area!

Ok, I don't see how you arrive at the results you expect.

I want to...list any occurences of values in
column 2 that appear in column 1, including multiples

And that's exactly what my formula does.

Based on this sample data you posted, what results do expect:

280 280
280 275
280 275
278 272
278 272
278 270
275 270
275 270
275 270
275 270
275 270
270 262
0 0
0 0
0 0
0 0

--
Biff
Microsoft Excel MVP


"Dingy101" wrote in message
...
Biff,

Thank You, as usual great answer.

It almost works, if there is such a thing.

At times, depending on data set in A2:B9 the last non 0 entry repeats the
number of times it appears in column B, but without avaliable matches in
column A.

Below is a cut & paste of actual data set, third column is the error trap
formula results.

What I would expect to see in fourth column is only a single 270 entry,
rest
looks good.


280 280 13 280
280 275 13 275
280 275 13 275
278 272 13 270
278 272 14 270
278 270 15 270
275 270 15 270
275 270 15 270
275 270 15 270
275 270 15 0
275 270 15 0
270 262 15 0
0 0 16 0
0 0 16
0 0 16
0 0 16


Gary



"T. Valko" wrote:

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


.



.