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

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

On 3 Mar 2005 06:10:04 -0800, (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

The Array formula:

=MAX((Account=31405535)*Date)

To enter an array formula, after typing or pasting in the formula, hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula.


--ron

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
.

=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