Excel VBA - look for close dates in column, add usage values and delete 1 date row 1

[renigar]

I work for a water utility in CA and with the drought we're doing various reports on usage. I've written a macro that takes output from our billing software and gets it close to a usable form. But the next step I want to do has me stumped. The small data sample I've shown here will have thousands of rows. Each Loc# is a water meter. Most Loc#s will have only 6 billing periods a year unless the account changes hands. Then there will be a final bill generated. For the purposes of my final report I need to have each location number have six billing periods for comparison with previous years. I would like to have code that will find duplicate instances of a month within each Loc#s billing periods (July in the sample below) add the values together then delete one row. This would solve most but not all my problems as we have had instances of accounts changing hands several times in a billing period. Any suggestions would be appreciated. (Underscores are spaces)

Loc# BillDate _Usage Count - In the count column Is a formula to count the instances of each Loc#
2123 01/05/15 __5____ 1
2123 03/04/15 _10____ 2
2123 05/05/15 _13____ 3
2123 07/03/15 _15____ 4__ The current 4 count row would be deleted after it's value is added to the next row
2123 07/21/15 __9____ 5__ This row becomes 2123 07/21/15 _24 ____4
2123 09/02/15 _15____ 6
2123 11/03/15 __8____ 7
2142 01/05/15 __4____ 1
2142 03/04/15 __7____ 2
etc

 

[SkipVought]

Hi,

WHY does July have data for two rows? Could there be more than two for any period?

What if one of the dates were an August date or June date?

In general I believe it's not a good business practice to destroy data. What you need can probably be accomplished via a report, rather than modifying the source data which ought to remain pristine. You'll just produce a summarized report in accordance with the business' reporting periods.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[renigar]

Hi Skip,
The loc# (Location Number) is a permanent number assigned to a water meter at a property. It is assumed the property will always need water so the location number never changes. Each location number has an account number assigned to whoever is responsible for payment. The account assigned to a particular Loc# can change as many times as the party responsible for payment changes. In my data example the billing period ending July 3rd got billed. Then the account holder moved stopping their water service on July 21 triggering a final bill for that account for usage from the 4th to the 21st.

There could be more than two in a period. The most I have seen in the data set I'm working with is three for a total of eight bills at one location in one year. Although it is possible that there could be more. That's why I used the count column, it serves as a flag for locations that have more than six billing periods.

Your third question about the August or June date is also possible. The June date I would assign to the third billing period and the August date to the fourth billing period. I figured I would just have to do some consolidation manually.

The data export I'm doing is for drought analysis only and does not destroy any data in the original database. The utility billing software we use has canned reports with no provisions for generating the kind of analysis we're doing for conserving water during the drought. So I export to Excel and find it much easier to work with. I don't know much about Sql and can't access the database directly.

Thanks for your time,
renigar

 

[SkipVought]

FYI, I had cataract surgery yesterday. Seeing better today but off to see the doctor. Will be in touch.

A summary sheet might be in order using a SUMPRODUCT() function. We'll 'see' what we can do. You might check out Structured Tables in the mean while, as this will aid in your analysis.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Make this table a Structured Table via Insert > Tables > Table. I named the table tMSTR

[pre]
Loc# BillDate Usage
2123 01/05/15 5
2123 03/04/15 10
2123 05/05/15 13
2123 07/03/15 15
2123 07/21/15 9
2123 09/02/15 15
2123 11/03/15 8
2142 01/05/15 4
2142 03/04/15 7
[/pre]

On another sheet, these headings, starting in A1...
[pre]
Loc# 01/01/15 03/01/15 05/01/15 07/01/15 09/01/15 11/01/15
2123
2142
[/pre]

In B2...
[tt]
=SUMPRODUCT((tMSTR[Loc#]=$A2)*(tMSTE[Billdate]>=B$1)*(tMSTR[Billdate]<C$1)*(tMSTE[Usage]))
[/tt]
...and Copy/Paste across and down through all rows/columns of headings/data.

Posted from my iPad. Not checked.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[renigar]

I couldn't get your sumproduct formula to work, kept getting an error. Wrote the following code this morning. It does most of what I want except the occasions where the extra bill period is not in the same month. Still thinking about that, maybe If-Then or Case. I added a column E with the formula to pull the month number from column B. Code runs a bit slow with the looping, but much better than the alternative.
__A____B_____C_____D_____E__
Loc# BillDate Usage _Count _Month
2123 01/05/15 _5_______1_____1
2123 03/04/15 10_______2_____3
2123 05/05/15 13_______3_____5
2123 07/03/15 15_______4_____7
2123 07/21/15 _9_______5_____7
2123 09/02/15 15_______6_____9
2123 11/03/15 _8_______7____11
2142 01/05/15 _4_______1_____1
2142 03/04/15 _7_______2_____3

Sub LoopTest2013()

    Range("E2").Select

' Do this loop while the active cell value is between 1 & 12
    Do While ActiveCell.Value > 0 Or ActiveCell.Value < 13
' Put the 1st Month value in current row column E into a variable
        Month_1 = ActiveCell.Value
' Put the 1st bill value in column C into a variable
        Bill_1 = ActiveCell.Offset(0, -2).Value
' Drop down 1 row
        ActiveCell.Offset(1, 0).Select
' Put the 2nd Month value in current row column E into a variable
        Month_2 = ActiveCell.Value
' Put the 2nd bill value in column C into a variable
        Bill_2 = ActiveCell.Offset(0, -2).Value
' Check if their are 2 instances of the same month
        If Month_1 = Month_2 Then
' Add the 2 months consumption and put input as the second months consumption
        ActiveCell.Offset(0, -2).Value = Bill_1 + Bill_2
' Go up 1 row and delete row
        ActiveCell.Offset(-1, 0).EntireRow.Delete

        End If

If ActiveCell.Value = Empty Then Exit Sub

' Go back to Do While
    Loop

End Sub

 

[SkipVought]

This is the corrected formula
[tt]
=SUMPRODUCT((tMSTR[[highlight #FCAF3E]Loc'#[/highlight]]=$A2)*(tMSTR[BillDate]>=B$1)*(tMSTR[BillDate]<C$1)*(tMSTR[Usage]))
[/tt]

Here are my results
[pre]
Loc# 1/1/15 3/1/15 5/1/15 7/1/15 9/1/15 11/1/15 1/1/16
2123 5 10 13 24 15 8
2142 4 7 0 0 0 0
[/pre]

Notice I had to add the next period after 11/1/15 and the formula only gets pasted through the next to last heading.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[renigar]

That looks great Skip. I'll try it tomorrow and let you how it goes. Thanks

 

[Andrzejek]

renigar,
I see you put a lot of effort to align your data (and it is appreciated), but you can just use Pre tag to do this:

[pre]
A B C D E
Loc# BillDate Usage Count Month
2123 01/05/15 5 1 1
2123 03/04/15 10 2 3
2123 05/05/15 13 3 5
2123 07/03/15 15 4 7
2123 07/21/15 9 5 7
2123 09/02/15 15 6 9
2123 11/03/15 8 7 11
2142 01/05/15 4 1 1
2142 03/04/15 7 2 3
[/pre]

Just a suggestion...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[renigar]

Skip, your formula did the trick. I don't quite understand how it's doing yet but will do a little research. Thank you so much for your time and knowledge.

Andy, thank you for your tip.