Get latest entries in list?

Smoerble
Smoerble used Ask the Experts™
on
I have a list:

Date - Name - Comment
1.2.2012 - Anna - Calls someone
2.2.2012 - Anna - gets a call
3.2.2012 - Anna - Is on vacation
1.2.2012 - Cindy - Calls Anna
3.2.2012 - Cindy - Dates John
1.2.2012 - Dirk - Is alone at home

I want to display for each person the latest activity, so I need a result like this:
3.2.2012 - Anna - Is on vacation
3.2.2012 - Cindy - Dates John
1.2.2012 - Dirk - Is alone at home

Can you do this with regular formula? Anyone has an idea how to do it please?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012
Commented:
Is your list all in one cell?  Or is the list in 3 cells (e.g., Columns A-C?)

Are your dates displayed with d.m.yyyy, or is it just text?

Dave

Author

Commented:
Attached an example file. Hope, this makes it more clear. The result in the list is NOT correct.
List-Test.xlsx
Most Valuable Expert 2012
Top Expert 2012
Commented:
Well, having a dataset makes things so much simpler!  Your sample in your original post appeared like the string had to be parsed.  Great!

Your formula works, I believe, if you make it an array formula (F2 to edit, then CTRL+SHIFT+ENTER to confirm array), however, here's a simpler one, as we need to find the date of the last comment/status as well:

If the person's name is in column F

For the date of the latest status update, use:

=MAX(IF($B$2:$B$100=$F2,$A$2:$A$100,0))

CTRL+SHIFT+ENTER to confirm, and copy down

Then, for the latest comment/status, use:

=LOOKUP(1,1/(($A$2:$A$100=$E2)*($B$2:$B$100=$F2)),$C$2:$C$100)

and copy down.

See attached.

Dave
List-Test.xlsx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial