Creating an unique list but applying 2 conditions...can it be done?

[annysjunkmail]

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

 

[Max]

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$22=D2))>1,"",ROW()))
Leave F1 blank

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

In H2:
=IF(ROWS($1:1)>COUNT(F:F),"",INDEX(D,SMALL(F:F,ROWS($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(MAX(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

 

[annysjunkmail]

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$22=D2))>1,"",ROW()))
Leave F1 blank

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

In H2:
=IF(ROWS($1:1)>COUNT(F:F),"",INDEX(D,SMALL(F:F,ROWS($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(MAX(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














- 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

 

[Charlie]

Based on how you describe what you want and the example data that you
provided I would start by resorting your data. Sort by RefID (Ascending)
first, then by ID (Ascending) next, then by Date (Descending) last. That
will organize all your similar records next to each other. The first record
in any group that has matching RefID and ID will be the one you want. I'll
leave it up to you (or another responder) to figure out the loop to keep the
top record of each group.

Good luck,
Charlie

 

[Max]

Not sure why. Perhaps try this sample file
with the earlier set of formulas implemented & working properly:
http://www.freefilehosting.net/download/3f1h2
Extract uniques based on 2 cols n by latest date.xls


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

 

[annysjunkmail]

Not sure why. Perhaps try this sample file
with the earlier set of formulas implemented & working properly:http://www..freefilehosting.net/download/3f1h2
Extract uniques based on 2 cols n by latest date.xls

--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---
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

Hi Max,
I know why it doesn't work. My ID numbers were formatted as text (the
original file comes from Access 2000 where the ID field is a text
field). After converting ID's to numbers it worked fine.
Thanks very much for your expertise and sample workbook.
A very clever solution indeed!

Regards
Chris

 

[Max]

Welcome, Chris. Glad you got it working.

Hi Max,
I know why it doesn't work. My ID numbers were formatted as text (the
original file comes from Access 2000 where the ID field is a text
field). After converting ID's to numbers it worked fine.
Thanks very much for your expertise and sample workbook.
A very clever solution indeed!

Regards
Chri