Selecting the latest record for transactions

[ahmedzia]

Hi,

I have data for items in transaction format and I need to pick up the
most latest information from tht transaction-formatted excel sheet.
E.g.

S. No. Name Location Status Date
1 Ahmed Karachi Depart 10-08-09
2 Zia Multan Depart 20-08-09
3 Zaidi Lahore Depart 25-08-09
4 Ahmed Islamabad Arrive 22-08-09
5 Zia Faisalabad Arrive 23-08-09
6 Zaidi Multan Arrive 28-08-09
7 Ahmed Islamabad Depart 01-09-09

Now the result I want is current status of Ahmed, Zia and Zaidi in a
separate sheet. Which means that the above data needs to be calculated
based on latest date. I tried to use vlookup but it returns only the
top most occurence and doesnt take into account the date factor.

Please help me in this regard.

 

[Max]

Assume your source table as posted is in A1:E8,
where dates in E2:E8 are real dates recognized by Excel

Assume you have the unique names listed in G2 down, eg: Zia, Zaidi, etc
Put this in H2, then press CTRL+SHIFT+ENTER
to array-confirm the formula (instead of just pressing ENTER):
=INDEX(C$2:C$8,MATCH(MAX(IF($B$2:$B$8=$G2,$E$2:$E$8)),IF($B$2:$B$8=$G2,$E$2:$E$8),0))
Copy H2 across to J2, fill down. Format J2 down as dates to taste.
This will return the expected results:

Zia Faisalabad Arrive 23-Aug-09
Zaidi Multan Arrive 28-Aug-09
Ahmed Islamabad Depart 1-Sep-09

Adapt the ranges to suit the actual extents of your source table.
Aha? Ring the stars in google
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[ahmedzia]

Thankyou max, I got the desired outcome by using your technique.

Thanks a lot to both of you.

 

[Max]

Welcome, glad to hear ..
Celebrate your success .. Ring the stars in google
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik