Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an unique list but applying 2 conditions...can it be done?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an unique list but applying 2 conditions...can it be done?
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 Singapore http://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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an unique list but applying 2 conditions...can it bedone?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an unique list but applying 2 conditions...can it be done
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 " wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an unique list but applying 2 conditions...can it be done?
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 Singapore http://savefile.com/projects/236895 xdemechanik --- <annysjunkmail@... wrote 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an unique list but applying 2 conditions...can it bedone?
On 8 Apr, 14:09, "Max" wrote:
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 --- <annysjunkmail@... wrote 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an unique list but applying 2 conditions...can it be done?
Welcome, Chris. Glad you got it working.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- <annysjunkmail@... wrote 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Unique List | Excel Discussion (Misc queries) | |||
Creating a unique list of Cost Codes in Col. A from all worksheets in all workbooks in folder X | Excel Programming | |||
Creating A Unique List of Values From A Table | Excel Worksheet Functions | |||
Sum unique with conditions | Excel Worksheet Functions | |||
Help creating pull down list with unique names and no blanks | Excel Programming |