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 wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200909/1