but simple is better right? : )

And simple is relative, right? <g

--
Biff
Microsoft Excel MVP


"minyeh" wrote in message
...
On Dec 8, 3:09 am, Dingy101
wrote:
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

.- Hide quoted text -

- Show quoted text -


Data in the range A2:B13
In cell C2, key in
=IF(COUNTIF(A:A,B2)COUNTIF(B$2:B2,B2)-1,B2,"")
fill down as far as u want
then do a filter to eliminate the blank cells,
or do a one-to-one mapping to another column if u want
but simple is better right? : ) hope this helps