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

 

[pogiman via OfficeKB.com]

Here's a non-VBA suggestion (if you like):

On the table below, add a column to with this formula:
A B C D E
1 S. No. Name Location Status Date
2 1 Ahmed Karachi Depart 10-08-09
3 2 Zia Multan Depart 20-08-09
4 3 Zaidi Lahore Depart 25-08-09
5 4 Ahmed Islamabad Arrive 22-08-09
6 5 Zia Faisalabad Arrive 23-08-09
7 6 Zaidi Multan Arrive 28-08-09
8 7 Ahmed Islamabad Depart 01-09-09

F2 =$B2&"-"&text(count($B$2:$B2),"00") ->then copy down

On the other sheet assuming you have the names you want and just the updated
status (assuming also that the updated status is alway entered/added at the
end of the table)

A B
1 Name Status
2 Ahmed =Lookup($A2&"-"&count(table!$B$2:$B$8),table!$F$2:$F$8,
table!$D$2:$D$8)
3 Zia
4 Zaidi

If there's a problem try this:
=Lookup(sumif(table!$F$2:$F$8,$A2&"-"&count(table!$B$2:$B$8),table!$A$2:$A$8),
table!$A$2:$A$8,table!$D$2:$D$8)

The 2nd formula is more reliable since it adjust even when the record is
moved to a different row (as long the column A of the table is in sequence).

Hope this helps.

 

[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