Last line in a list
 

Hi All,

I have two columns in a spreadsheet, column A - a list of dates on which
transactions took place. Column B the value of the transaction.
More than one transaction can take place on one day.
I can have a list of months somewhere on the sheet if that will help.
What I want is to get the value of the last transaction for each month.

Eg.

1/4/04 100
2/4/04 200
3/4/04 100
4/4/04 400
4/4/04 500 Last transaction
1/5/04 600
2/5/04 300
28/5/04 400 Last transaction
1/6/04 500
28/6/04 100 Last transaction


Many thanks

Jim

Hi

Add a 3rd column, p.e. column C with formula in it (example for cell C2)
=AND(YEAR(A3)=YEAR(A2),MONTH(A3)=MONTH(A2))
and copy it to same range as data in columns A:B. Let us assume you have now
3-column table with data in range A2:C100
To get last transaction of 4th month of specific year (2004):
=SUMPRODUCT(--(YEAR(A2:A100)=2004),--(MONTH(A2:A100)=4),--(C2:C100=FALSE),B2
:B11)
when all data are of same year, then you can simplify the formula:
=SUMPRODUCT(--(MONTH(A2:A100)=4),--(C2:C100=FALSE),B2:B11)


Arvi Laanemets


"Jimbola" wrote in message
...
Hi All,

I have two columns in a spreadsheet, column A - a list of dates on which
transactions took place. Column B the value of the transaction.
More than one transaction can take place on one day.
I can have a list of months somewhere on the sheet if that will help.
What I want is to get the value of the last transaction for each month.

Eg.

1/4/04 100
2/4/04 200
3/4/04 100
4/4/04 400
4/4/04 500 Last transaction
1/5/04 600
2/5/04 300
28/5/04 400 Last transaction
1/6/04 500
28/6/04 100 Last transaction


Many thanks

Jim

Assuming your dates are all sorted in ascending order, then create a list
somewhere listing 1 to 12, eg in say E1:E12. Now assuming your data is in
A1:B500, then in cell F1 put the following

=IF(ISNA(LOOKUP(2,1/(MONTH($A$1:$A$500)=E1),$B$1:$B$500)),"",LOOKUP(2, 1/(MON
TH($A$1:$A$500)=E1),$B$1:$B$500))

and then copy down to F12.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Jimbola" wrote in message
...
Hi All,

I have two columns in a spreadsheet, column A - a list of dates on which
transactions took place. Column B the value of the transaction.
More than one transaction can take place on one day.
I can have a list of months somewhere on the sheet if that will help.
What I want is to get the value of the last transaction for each month.

Eg.

1/4/04 100
2/4/04 200
3/4/04 100
4/4/04 400
4/4/04 500 Last transaction
1/5/04 600
2/5/04 300
28/5/04 400 Last transaction
1/6/04 500
28/6/04 100 Last transaction


Many thanks

Jim