FIND LAST VALUE FROM EACH MONTH'S DATA

[NH]

I've a spreadsheet with 2 columns of data containing dates and interest rate
respectively
I want to extract the last rate for each month. The spreadsheet looks like
this

Date Rate
5/1/04 2%
5/7/04 2.01%
6/1/04 3%
6/15/04 3.12%
7/1/04 3.5%

From the above the output should eliminate the first and 3rd entry they are
not the last rate for that month

 

[Frank Kabel]

Hi
use a helper column with the following array formula
(entered with CTRL+SHIFT+ENTER):
=IF(A1=MAX(IF(MONTH(A1)=MONTH
($A$1:$A$100),$A$1:$A$100)),1,0)
and filter the zeros out

 

[Don Guillett]

Will this help

Sub lastinmonth()
For Each c In Range("a2:a6")
If Month(Cells(c.Row + 1, 1)) > Month(Cells(c.Row, 1)) Then
MsgBox Format(c.Offset(, 1), "0.00%")
End If
Next
End Sub

 

[Don Guillett]

Change the a1's to a2's if you have a header (as your example)

 

[Don Guillett]

if you want the month also, use

Sub lastinmonth()
For Each c In Range("a2:a6")
If Month(Cells(c.Row + 1, 1)) > Month(Cells(c.Row, 1)) Then
MsgBox "for " & Format(c, "mmm ") & Format(c.Offset(, 1), "0.00%")
End If
Next
End Sub

 

[Aladin Akyurek]

Let A2:B6 house the sample you provided (excluding labels).

=LOOKUP(2,1/((MONTH($A$2:$A$6)=E2)*(YEAR($A$2:$A$6)=F2)),$B$2:$B$6)

where E2 houses a month number like 5 and F2 a year like 2004.