Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last record in month for each of several types of record.
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 __________________________ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last record in month for each of several types of record.
Why not ask in worksheet.functions
news://msnews.microsoft.com/microsof...heet.functions -- Regards, Tom Ogilvy "Richard Buttrey" wrote in message ... 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 __________________________ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last record in month for each of several types of record.
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" wrote: 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 __________________________ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last record in month for each of several types of record.
On Sat, 2 Apr 2005 22:14:23 -0500, "Tom Ogilvy"
wrote: Why not ask in worksheet.functions news://msnews.microsoft.com/microsof...heet.functions Thanks Tom, That's a new one to me. I'll do as you suggest Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last record in month for each of several types of record.
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 On Sat, 2 Apr 2005 22:25:06 -0800, gocush /delete wrote: 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" wrote: 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 __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last record in month for each of several types of reco
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 "Richard Buttrey" wrote: 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 On Sat, 2 Apr 2005 22:25:06 -0800, gocush /delete wrote: 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" wrote: 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 __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the next record in a column | Excel Discussion (Misc queries) | |||
Open new record with selected fields from previous record | Excel Discussion (Misc queries) | |||
Finding Duplicate Record in more than one column | Excel Discussion (Misc queries) | |||
Record Macro - Record custom user actions | Excel Programming | |||
Need help autopopulating next new record with previous record data | Excel Programming |