filtering

Posted by glhoek on September 29, 2001 7:36 AM

I have a spreadsheet with column names lastname, lab value, and date lab performed. I need to know the lab value for the latest date lab performed for each lastname.
lname date value
ch 12/16/2000 7
ch 5/31/2001 7.1
la 3/15/2001 6.6
el 2/5/2001 6
el 8/24/2001 6
gr 4/10/2001 6.8
fa 8/7/2001 7.7
be 2/12/2001 6.8
be 8/29/2001 6.3
sa 11/15/2000 6.6
sa 12/18/2000 5.8
sa 3/19/2001 6.2
sa 8/16/2001 6.8
I need the above list to look like this.

lname date value
ch 5/31/2001 7.1
la 3/15/2001 6.6
el 8/24/2001 6
gr 4/10/2001 6.8
fa 8/7/2001 7.7
be 8/29/2001 6.3
sa 8/16/2001 6.8

many thanks
greg

Posted by Aladin Akyurek on September 29, 2001 8:34 AM

Greg,

I'll assume your sample data to be in A1:C14, including labels.

Create a uniqe list of clients say in E from E2 on. Thus, E1=lname, F1=date, and G1=value

In F2 array-enter: =MAX(IF($A$2:$A$14=E2,$B$2:$B$14))

You need to hit CONTROL+SHIFT+ENTER at the same time, not just ENTER in order to array-enter a formula.

In G2 enter: =INDEX($C$2:$C$14,SUMPRODUCT(MATCH(E2&F2,$A$2:$A$14&$B$2:$B$14,0)))

Select F2:G2 and copy down as far as needed.

Aladin

PS. If you'd like to have a copy of the workbook showing the above, drop me a line.

============