Excel macro to allocate a list of values with different duration to hours of the year

Hello!

I am currently writing my Masters thesis and need to organize data for the empirical research, which I've mostly done apart from this particular type of data.

There is a list of power plant outages that took place during the year, indicating the starting time and duration of the outages of various power plants, as well as the amount of electricity not available due to each of the outages. See file attached for first two days of the year as an example. Example 3.xlsx I need to find the amount of unavailable electricity for each hour of the year.

I need a formula that will take the value from column C (unavailable capacity) on the sheet "List of outages" and transfer it into the column H of the sheet "Outages sorted by hours" in the amount of cells displayed in the column E "Duration" (sheet "List of outages") starting with the hour displayed in D "Outage begin" (sheet "List of outages") and further on. For every unique hour in the column A (sheet "Outages sorted by hours") there might be more than one corresponding value so that they are to be added up in the resulting column. I've calculated manually how the results and the logic look like.

I tried to solve the problem by using formulas but failed to find a solution. Could you please help me to do it in VBA? I think there might be a fairly simple solution but I am still not good enough in VBA to do it. Thank you in advance!

Regards, Michael

This was fun to think about implementing. Hope your thesis goes well.

Please Login or Register  to view this content.

Hello! Thank you for the code and readiness to help, I appreciate!

The code indeed works with the given file, however when I am trying to apply it to the whole dataset by replacing cell ranges in your codes with larger ones, it doesn't seem to work...

Anyways I found a non-VBA solution to the problem. Still thank you very much walruseggman!

No problem. And if you ever do need help adjusting the code, just let me know.