A Challenge Hourly Report

Hi Logit
Here is the problem I have that you may help me to create the report I need... My knowledge of VBA is as a beginner and think this report requires much more experience and knowledge of programming. Here is the situation: I have a list of Plant Generators and a list of data info regarding the energy (KWh) delivered to the system. The info, each lecture, is sent every 15 minutes everyday. It means that in a month, let's say January, the monthly report for every generator needs to be 2,976 records (31 days x 24 hrs/day x 4 times/hr), there are 40 generators, so the list is huge (around 119k records), but I have just attached the file with a little portion as sample.

The report for every generator needs to be minimized in an hourly version adding all the kwh delivered every 15 minutes of the hour. It will be an easy task IF the records stay perfect (4 records every hour) since it would only be necessary to have a loop that adds the records every 4 times; however, sometimes a record is missing and it is when it gets complicated because you cannot add the first record of the next hour but instead to add only the 3 records within that hour and so for. At the end, a report of exception (missing records) need to be created.

I am attaching the file where I have done the programing but as a total version (not hourly), IF possible, could you help me on this? Any kind of help either programming or idea of how to do it, will be appreciated. Thanks

Re: A Challenge Hourly Report

Thanks Carim, but that is a formula in the worksheet, I need the macro to do that and get the hourly report.

Thanks

Re: A Challenge Hourly Report

Hi Claim
I worked the macro to do the report. However, it is without considering the missing records. I intentionally deleted 1 record to check if the macro considers it but it is fixed to read and summarize every 4 records independently of the time period. Could you please help me on fixing the macro to read 4 only the records within the time of 1 hr as I explained in the first post? Also, my macro is not completing the last 2 records at the end of the report. Thanks

Re: A Challenge Hourly Report

Quote from Carim;788037

Hello Edmundo,

Congratulations on your macro ... !!!

You are an Expert ... !!! :wink:

As a starting point ... attached is a small modification ...

Hope this will help

:smile:

Display More

Thanks Carim, it is fantastic! your are fast!. Just one thing... the data for the missing record is not included in the report. For example, if there needs to be 4 records withing 1 hr (15 min each) and one record is missing, the macro need to only account for the rest records, in this case 3 records instead of 4 and add the answer in the report. In your code, it is not reported at all. The report is missing the 15,110.61 KWh missing for the 3 records from 4:00 to 4:45. In other words, it always need to read, add the KWh and include it in the report no matter how many records (1,2,3, or 4 maximum). It will only be not reported in the case the 4 records are missing.

Thanks

Re: A Challenge Hourly Report

Hi Carim
I have tried to use this version but I do not understand why the values are 0... can you please review and help?

Re: A Challenge Hourly Report

Hello Carim

The report is based on an hourly segments. Each segment has to have (ideally) 4 records (every 15 min). If 1 or more records are missing, I need to get the total of the remaining records. Example: if 2 records are missing, get the total of the others 2 records and continue with the next segment, BUT do not take the missing records out of the next segment. Hope this illustrate better my needs. Thanks for your help.

Re: A Challenge Hourly Report

Hello Carim

I found a way to overcome the missing records!!. It is creating another macro (subroutine) to first go over all records and find the missing records (if difference in time is 15 min) and inserting lines for every missing record, and then we can run the report for summarizing the hourly data for every 4 records since now we will be sure there will always be 4 records for every segment of 1 hr.

The only little problem I have now is when the plant (column B "idclient") changes to a new Plant (see row # 2974) because the previous Plant already got their 31 days and new Plant will start on day 1 where the conditions of 15 min will not longer be valid. How to do this? maybe using a While...end loop? Can you help me to add it to the macro that insert lines?

Thanks .

Re: A Challenge Hourly Report

Hello
Thanks, that was a Smart approach and I think you are getting very close to the final solution :wink: I said this because the macro is taking the records of next segment of hrs instead of the missing records. I think the variable D you added will not always test x+3 because of the missing record. I think your latest versión would be wonderful if adding the row of the missing record, maybe a combination of your latest versión and mine. I am glad for all your help!

Re: A Challenge Hourly Report

Hola Carim

Energia looks perfecto but if manually you add up the total energía of the segment of missing records (ejemplo: only 3 records instead of 4) you will notice it is not what comes out in the report. You can intentionally erase some records (either 1, 2, or 3 of the segment of 4 record/hr) you will notice the total reported if not the sum of the remaining records of the same segment, it is taking the records of next segment. My latest versión can be tweaked, but if I do not resolve the problema of the change of "idClient", it will not work (will probably give me and endless loop). But I will keep trying.

Saludes, y gracias por su ayuda..

Re: A Challenge Hourly Report

Hi Carim
Thank, I am done with my project, I finally found the way to fix all of my programing problems. I used a sample of the data to run it and it ran perfect. However, now that I want to run it with the entire set of data (104,226 records) the subroutine "InsertarLineas" is giving me an "overflow" error. I even declared the lastrow variable to "Long" but I am still getting the error. Is there any other variable that is affecting the overflow? I even test it declaring the variable "X" to "Long" too and still the same overflow error. Why the subroutine "HourlyReport", if desactivating calling the "InsertarLineas" subroutine, is not giving the same error if going thru the same amount of data set?

I cannot load the file with all set of data because of size limit, so I am attaching it with the sample size. Thanks

Re: A Challenge Hourly Report

Thanks
Yes, it now runs but I got an endless loop. Can you check the code I sent you yesterday? It is strange becuase with the sample data set (6,000 records or more) it runs perfect, but with the entire data set (104K records) it gets stuck. Thanks