matching last 2 columns with date

[magnus101]

Hello,
I have a table with columns
Name, Date, % Complete

I would like to grab the Name/Date combo with the last date. Then get the % complete.

My data could look like this

Name, date, %complete
a, 1-1-2012, 10%
b, 1-5-2012, 20%
a, 1-6-2012, 20%
b, 2-1-2012, 30%
c, 2-1-2012, 10%
a, 2-3-2012, 30%

so I would want to grab
b, 2-1-2012, 30%
c, 2-1-2012, 10%
a, 2-3-2012, 30%

I can create a pivot table with Name and max of date. That gives the Name/Date combo.
But then I cannot get the % complete.

anyone have ideas?

thanks

[DGagnon]

Have alook at the attached, i used Max(if... to determin the latest date for each name, then sumifs to find the % complete for that combo.

the max if formula is an array formula, so needs to be confimred with Ctrl+Shift+Enter and is:
=MAX(IF(A2:A7=F2,B2:B7,0))

the other is a standard sumifs
=SUMIFS(C:C,A:A,F2,B:B,G2)

[oeldere]

Maybe like this?

[magnus101]

Thanks DGagnon, works great

[vlady]

@ Magnus101

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as "SOLVED".This is an obligation of all members to mark their own thread "Solved" so please do so.

For the meantime I'll do it for you.

How?
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
**You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.