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