Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Last Date in List
Hi
I have a table set out as follows, Account Date Text Amount 31405535 01/01/04 nnnn 100000 31405535 12/12/03 tttt 222222 31405535 03/03/05 ttt 122456 There are 5 different accounts which could be entered under Account. The list is a export from out bank statement, I would like a formula to display the last date of transaction per account number. For eg in the above list I would like the formula to return 03/03/05. Can anyone help? TIA |
#2
|
|||
|
|||
Enter your list of account numbers in a column, let's say Column F,
starting at F2... G2, copied down: =LOOKUP(2,1/($A$2:$A$100=F2),$B$2:$B$100) Hope this helps! In article , (Edgar Thoemmes) wrote: Hi I have a table set out as follows, Account Date Text Amount 31405535 01/01/04 nnnn 100000 31405535 12/12/03 tttt 222222 31405535 03/03/05 ttt 122456 There are 5 different accounts which could be entered under Account. The list is a export from out bank statement, I would like a formula to display the last date of transaction per account number. For eg in the above list I would like the formula to return 03/03/05. Can anyone help? TIA |
#3
|
|||
|
|||
|
#4
|
|||
|
|||
An AutoFilter would work well here. With account #'s in
col. A and dates in col. B, create a new col. called "Max" in col. E and copy this into E2: =B2=MAX(($A$2:$A$100=A2)*$B$2:$B$100) After copying, press ctrl + shift + enter, and fill the formula all the way down. Now select the 5 columns and go to Data Filter AutoFilter and filer for TRUE in col. E. HTH Jason Atlanta, GA -----Original Message----- Hi I have a table set out as follows, Account Date Text Amount 31405535 01/01/04 nnnn 100000 31405535 12/12/03 tttt 222222 31405535 03/03/05 ttt 122456 There are 5 different accounts which could be entered under Account. The list is a export from out bank statement, I would like a formula to display the last date of transaction per account number. For eg in the above list I would like the formula to return 03/03/05. Can anyone help? TIA . |
#5
|
|||
|
|||
=MAX(IF(A1:A10=31405535 ,B1:B10))
which is an array formula;a so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "Edgar Thoemmes" wrote in message om... Hi I have a table set out as follows, Account Date Text Amount 31405535 01/01/04 nnnn 100000 31405535 12/12/03 tttt 222222 31405535 03/03/05 ttt 122456 There are 5 different accounts which could be entered under Account. The list is a export from out bank statement, I would like a formula to display the last date of transaction per account number. For eg in the above list I would like the formula to return 03/03/05. Can anyone help? TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to make excel select todays date from a list | Excel Worksheet Functions | |||
Need to find oldest date in ever changing list. | Excel Worksheet Functions | |||
In Excel, why sort function fails when applied to a list of date? | Excel Discussion (Misc queries) | |||
I have a list of dates that I need to count based on a date range | Excel Worksheet Functions | |||
How can I sum information in a list with a date range? | Excel Worksheet Functions |