Selecting the latest record for transactions

  • Thread starter Thread starter ahmedzia
  • Start date Start date
A

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.
 
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
 
Thankyou max, I got the desired outcome by using your technique.

Thanks a lot to both of you.
 
Back
Top