Removing empty cells from column

[NDS123]

Hello,

I fail to figure out how I can group the values of column Q ("kWh/uur") to the format of column L.
I want the empty cells to be removed automatically and only cells with a value to display like they do in column L.
Right now it's just a copy paste but these values should adapt automatically when I change the yearly consumption in R2.

Kind Regards

[Pete_UK]

It's not very easy to read your picture, but you seem to be using SUMIFS only when there is a change of HOUR in column A. If you want the cells bunched up, then will you have another column to record the hour (otherwise, it wouldn't make much sense)?

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Do not try to use the Paperclip icon for attachments, as it doesn't work on this forum.

Hope this helps.

Pete

[NDS123]

Can you use this file?

[davsth]

What does your solution want to look like? It is not clear from the attachment, as you do what Pete has suggested as the solution!

[NDS123]

In column L I would like the Hourly summations to be bunched up without empty cells.

[Pete_UK]

I've used columns T for the date, U for the time, and V for the kWh/hr, using these formulae in the cells stated:

T2: =INT(MIN(A:A))
U2: =MROUND(MOD(MIN(A:A),1),1/24)

These get the starting points, and then:

T3: =T2+(U3=TIME(0,0,0))
U3: =IF(U2=TIME(23,0,0),0,U2+TIME(1,0,0))

Column T is formatted as a date, and column U as a time. Note that the time gets reset to zero at each change of day, in order to reduce errors from the increasing TIME function. The formulae in T3 and U3 can be copied down as far as you need them, though I have only copied them down a few rows in the example file attached.

Then you can use this formula in V2:

=SUMIFS(K:K,A:A,">="&T2+U2,A:A,"<"&T2+U2+TIME(1,0,0))

Copy down as required.

I'm not sure what all your other columns are for, but you can delete some of them if they just relate to getting the consumption at hourly intervals, as you can see that these formulae only need data from columns A and K.

Hope this helps.

Pete

[NDS123]

This worked! Thank you very much!!

[Pete_UK]

You're welcome - glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete