Selecting the latest record for transactions
 

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.

Selecting the latest record for transactions
 

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

Selecting the latest record for transactions
 

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

Selecting the latest record for transactions
 

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

Thanks a lot to both of you.

On Sep 2, 12:41*pm, Max wrote:
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
Singaporehttp://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

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

Selecting the latest record for transactions
 

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
---
"ahmedzia" wrote in message
...
Thank you max, I got the desired outcome by using your technique.

Thanks a lot to both of you.