On 8 Apr, 11:23, "Max" wrote:
One formulas play ..

Source data in cols A to E as posted, from row 2 to 30000
First, switch it to manual calc mode

Then place
In F2:
=IF(A2="","",IF(SUMPRODUCT((A$2:A2=A2)*(D$2:D2=D2) )1,"",ROW()))
Leave F1 blank

In G2:
=IF(ROWS($1:1)COUNT(F:F),"",INDEX(A:A,SMALL(F:F,R OWS($1:1))))

In H2:
=IF(ROWS($1:1)COUNT(F:F),"",INDEX(D:D,SMALL(F:F,R OWS($1:1))))

In I2, array-entered (press CTRL+SHIFT+ENTER to confirm the formula):
=IF(COUNT($G2:$H2)<2,"",INDEX(A$2:A$30000,MATCH(MA X(IF(($A$2:$A$30000=$G2)**($D$2:$D$30000=$H2),$B$2 :$B$30000)),IF(($A$2:$A$30000=$G2)*($D$2:$D$30000= $*H2),$B$2:$B$30000),0)))
Copy I2 to M2. Select F2:M2, fill down all the way. Press F9 to recalc.
Hide away cols F to H. Format col J as date. Cols I to M should return the
required results
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
wrote in message

...



Hi Group,

I have searched previous posts but none seem to fit my requirements.
What I am trying to do is to create to return a list of unique records
(RefID) which must match the following conditions:

1) Unique record by ID, and then,
2) Most recent date only.

So, for example, I need sample which looks like this:

RefID Date Target ID Value
003715 31-Mar-07 X 003332 9
003715 31-Mar-06 X 003332 5.5
004248 30-Jun-06 Y 003101 14.5
004248 30-Jun-05 Y 003101 11.5
004248 31-Mar-04 Y 003101 15.5
004352 31-Dec-07 Y 003101 1.5
004352 31-Mar-05 Y 003101 1
004352 30-Sep-04 Y 003101 2
006608 30-Sep-07 Z 003423 7.5
006608 31-Mar-07 Z 003423 10.05
006608 30-Jun-06 Z 003423 10.5
006608 30-Jun-05 Z 003423 5
006608 31-Mar-05 Z 003423 4
012409 31-Mar-07 A 013329 0.25
012409 31-Dec-06 B 000189 0.5
012409 31-Mar-06 B 000189 1.5
012409 31-Mar-05 A 013329 1.5

To end up like this:

RefID Date Target ID Value
003715 31-Mar-07 X 003332 9
004248 30-Jun-06 Y 003101 14.5
004352 31-Dec-07 Y 003101 1.5
006608 30-Sep-07 Z 003423 7.5
012409 31-Mar-07 A 013329 0.25
012409 31-Dec-06 B 000189 0.5

Please note in particular RefID 012409 which rightfully occurs twice
as the ID is unique for that application.

Very complicated but it's what I need. *Very appreciative for all help
as I have an Access 2000 list of 30,000 records to 'filter' like this.

Thanks
Chris- Hide quoted text -

- Show quoted text -

Hi Max,
This is a very clever approach...I thought it was VBA specific but I
cannot get it to work properly.
I am entering your formulas as described in the correct order. Cols
F:H work correctly in that they return values but when I enter the
array it shows blanks?
Any thoughts?
Thanks for your time

Chris