Hi Domenic,

Solution is Great! Thank you very much for your time and assistance.

Cheers,
Sam

Domenic wrote:
First, define three separate dynamic ranges. One for the column
containing the ID, one for the column containing the Date, and one for
the columns containing your values (all five columns). Once you've done
that, try the following...

A1: enter your criteria/numeric value

B1:

=SUM(--(MMULT(--(Values=A1),TRANSPOSE(COLUMN(Values)^0))0))

...confirmed with CONTROL+SHIFT+ENTER

C1, copied down:

=IF(ROWS(C$1:C1)<=$B$1,INDEX(ID,SMALL(IF(MMULT(--(Values=$A$1),TRANSPOSE(
COLUMN(Values)^0)),ROW(ID)-MIN(ROW(ID))+1),ROWS(C$1:C1))),"")

...confirmed with CONTROL+SHIFT+ENTER

D1, copied down:

=IF(ROWS(D$1:D1)<=$B$1,INDEX(Date,SMALL(IF(MMUL T(--(Values=$A$1),TRANSPOS
E(COLUMN(Values)^0)),ROW(Date)-MIN(ROW(Date))+1),ROWS(D$1:D1))),"")

...confirmed with CONTROL+SHIFT+ENTER.

In addition, you can also define a reference (Insert Name Define)
for this part of the formula...

MMULT(--(Values=$A$1),TRANSPOSE(COLUMN(Values)^0))

...and replace it with the name you've given it.

Hope this helps!

Hi Domenic,

[quoted text clipped - 15 lines]
Cheers
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/1