Fill data serie in a pivot table

[Square34]

Hi all,
I have a data source sheet with multiple entry by days of the month but not for all days of the month.
Eg following:

1. Validity Data(d.m.y) Hours(dec)
2. @08@ 02.01.2019 2.75
3. @08@ 02.01.2019 3.00
4. @08@ 03.01.2019 3.00
5. @08@ 03.01.2019 3.00
6. @08@ 03.01.2019 1.40
7. @08@ 04.01.2019 1.00
8. @08@ 04.01.2019 4.00
9. @08@ 07.01.2019 3.00
10. @08@ 07.01.2019 2.90
11. @08@ 07.01.2019 3.00
12. @EB@ 10.01.2019 3.00
13. @EB@ 10.01.2019 3.00
14. @08@ 10.01.2019 2.00

I need to create a pivot which sum “Hours” by “Data” when “Validity”=@08@ but for all days of the month even if they are not present like following:

• Date TOTH
• 01.01.2019 0.00
• 02.01.2019 5.75
• 03.01.2019 7.40
• 04.01.2019 5.00
• 05.01.2019 0.00
• 06.01.2019 0.00
• 07.01.2019 9.90
• 08.01.2019 0.00
• 09.01.2019 0.00
• 10.01.2019 2.00
• 11.01.2019 0.00
• 12.01.2019 0.00
  …

I manage a pivot with daily sum and Validity=@08@ can be as filter, but i cannot manage to fill days with no data from the source sheet.

Thanks in advance for any sggestion

[Lupp]

See this attached example sheet for a raw suggestion by the tinkerer. You probably will need to move the helper part without time values to higher or lower row numbers I assumed 10000 datasets per month should be enough.

The heplers must be in the VALIDITY and in DATE column respectively, of course.

[Square34]

Hi Lupp,

Thanks a lot for your suggestion, it work fine.
While I’m trying to find a completely automated solution…
I have to do this each month for the previous month, and with your suggestion, each time I have “modify” the source table and “enrich” it with the monthly full day (your helper part work also well), then I have to create the new pivot.

If possible I need to leave the source sheet untouched (it can be a read-only sheet), and I was wondering if there’s a way to let pivot calculate values to list in rows while those are in series.

Anyway you save my day

[Lupp]

Quoting @Square34: “…While I’m trying to find a completely automated solution… I have to do this each month for the previous month, and with your suggestion, each time I have “modify” the source table and “enrich” it with the monthly full day (your helper part work also well), then I have to create the new pivot.”
You will surely use an otherwise empty sheet (copying a kind of template) then. You will, however need to adapt the sourtce range the by ‘Edit Layout’. The helpers don’t need manual adaption… Thjey adapt correctly based on the date in C2.
But: Even more autoiamtion you only can achieve with specialised ueser code…I doubt whether that pays.