Finding last record in month for each of several types of record.

[Richard Buttrey]

I have a database consisting of several types of record, (each
identified with a unique code), and each record has a date field.

The database has a varying number of records in each month, i.e not
necessarily one for each day.

I need to be able to identify the last record of each month for each
type of record, and sum the various value fields of each record type
across a range of months.

Can anyone suggest an Excel formula, which could be put outside the
database in a summary table, which could achieve this?

Usual TIA

Rgds


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Tom Ogilvy]

Why not ask in worksheet.functions

news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions

 

[gocush]

Richard
Does this work for you:

In your database, include a field for Type. Say for discussion you have
types A, B, C and D. Each record would have one of these types.
Also add a column for the Month. Say your Date field is in Col C. And Col
D is the Month field. If you db fieldnames are in row 1 and data starts in
row 2 then in D2 enter: =Month(C1) and copy this down col D

Then Sort your db first on your Type col, with the Month col as your
secondary sort key and the Date as the 3rd sort key.
Then do a Subtotal of the db, inserting a subtotal for each change in the
Month col.

If you do the above with you vb recorder turned on you will get the
necessary code to automate it ( probably with a little tweaking).

 

[Richard Buttrey]

Why not ask in worksheet.functions

news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions

Thanks Tom,

That's a new one to me. I'll do as you suggest

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Richard Buttrey]

Hi,

Thanks for the idea. Unfortunately it's not quite what I want.

What I need is the sum of the values, of the last record in each
month, for each type. i.e. I'm not interested in all the individual
daily records only the final record in each month.

All the records are themselves cumulative bank accounts which is why
I'm just trying to get the last reported record in each month, and add
these up to create a cumulative year to date value. Summing two month
end records at the end of month 2, three month end records at the end
of month 3, etc..

The last record in a month may not necessarily be on the last day of
the month.

Regards

Richard

Richard
Does this work for you:

In your database, include a field for Type. Say for discussion you have
types A, B, C and D. Each record would have one of these types.
Also add a column for the Month. Say your Date field is in Col C. And Col
D is the Month field. If you db fieldnames are in row 1 and data starts in
row 2 then in D2 enter: =Month(C1) and copy this down col D

Then Sort your db first on your Type col, with the Month col as your
secondary sort key and the Date as the 3rd sort key.
Then do a Subtotal of the db, inserting a subtotal for each change in the
Month col.

If you do the above with you vb recorder turned on you will get the
necessary code to automate it ( probably with a little tweaking).

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[gocush]

Ok. Start with my first reply to get the data sorted by month.

Then add another column to the right of the month col. This col will be
used to flag the records which are the "Last Record of the Month"
For my test I made this Col E, and I inserted in Col F some random amounts
which will represent your month-to-date totals. The following procedure will
flag each of these records with "True", then will filter the entire data
range, hiding all records which do not contain "True" in col E.

Then you can use the xl SUBTOTAL function on the filtered data. In my test
I only used data in rows 2-9. Below this (in F12) I entered the following
formula:

=SUBTOTAL(9,F2:F9)
The first argument of this function - 9- will get the sum of the range
F2:F9, EXCLUDING records which have been filtered out. See Excel Help on
SUBTOTAL for other statistical functions available by changing the 9 to other
numbers.

Option Explicit

Sub TotlLastOfEachMo()
Dim LastRow As Long
Dim RngMonth As Range
Dim oCell As Range

LastRow = Range("D65536").End(xlUp).Row 'Col D =Month
Set RngMonth = Sheets("Sheet1").Range("D2", Cells(LastRow, "D"))
'Sort Data on Month if needed

'Flag each "LastRecordOfMonth"
For Each oCell In RngMonth
If oCell <> oCell.Offset(1, 0) Then 'look for change in month
oCell.Offset(0, 1) = True 'flag the last rec of month
Else
oCell.Offset(0, 1) = ""
End If
Next
Range("myData").AutoFilter Field:=3, Criteria1:="TRUE"
'The 3 represents the 3rd col of MyData
'where I had the Last record of mo Flag
'Adjust to fit.

End Sub

Range("myData").AutoFilter
can be used to UN filter the data

Also you can hide the columns for Month and the Flag col if you wish