Read data in one table, then distribute and sum it in another table

Hello -

I'm not an Excel power user. I need a formula or VBA strategy to progressively go through departmental vacation entries in one table (start dates and end dates), and incrementally transfer and sum it by week and dept into another table in a different sheet. I have written VBA code to generate the 2 sheets contained in my simplified example file. Now I want to translate all the table entries in the "Data" sheet t to the table in the "Summary" sheet, organized by "Week Of" and "Department". Not sure if I need VBA or not. A big issue is that I'm not sure how to represent/account for the weeks between the start week and end week, since they are not explicitly given in the first table. It seems like I need to use loops, but that could get ugly and slow if I have to do it cell by cell for the table in the "Summary" sheet. Is there a more elegant approach, like using arrays? I'm not super familiar with arrays, so if that's a good choice a brief rationale for using them would be appreciated.

Ultimately I will read data from the resulting table to another workbook, where week by week dept headcount analysis takes place, so I want to represent all weeks between the first and last weeks in the table, even if they contain zeroes.

Thanks in advance for your help!

PWM

Hi Marvin - thanks for your help. I had thought about Pivot Tables but I don't think it will work.

Take the first entry for example: a vacation with startdate 8/31/2018 and end date 9/14/2018. The pivot table records that entry as a "1" on Aug 27 under dept "Bakery". However, there's more data that isn't being represented. That one vacation consists of:

1. 1 day off on Fri 8/31 in the "week of" 8/27 (FDOWO = 8/27- I have a key for the meaning of the headers below the first table.)
2. 5 days off the last "week of" which is week of 9/10
3. 5 days off the week between 8/27 and 9/10, which is week of 9/3 - this is one of the problems I have in that this week isn't explicitly represented in the table anywhere

So in addition to the 1 day for week of Aug 27 under Bakery, there should also be 5 days for week of 9/10 under Bakery and 5 days for week of 9/3 under Bakery. That's a total of 11 business days of vacation. So I don't think the PivotTable approach can get to this.

Plus, in the third entry, which is for a separate Bakery entry, there is another day of vacation in week of 9/3 which would need to be added to the 5 days from the first entry, totally 6 days off week of 9/3 in the Bakery dept.

I find this really complex, and not sure what approach could allow me to add the data for each entry to the new table, and then revise that data as necessary as subsequent entries in the first table are parsed.

Any additional idea you or anyone else has would certainly be appreciated.

Thanks,
Patrick

Hi MarvinP and FlameRetired - thank you for your replies, and I apologize for not acknowledging them sooner. As it turned out, MarvinP's reply got me on a better track, and the article he links to is both supersimple, yet profound especially if you're new to VBA. I was just accepting that the data I was receiving (a legacy of an Outlook workflow with only start and end dates specified) was what I had to deal with, and made the rookie mistake of trying to make my code conform to it. Rather than that, the article reminded me that a key part of strategy is to step back and assess the data, and then MANIPULATE it into a form that makes everything easier downstream. The idea of flat, tabular data was something I "sort of" understood but not really in the explicit manner that the article articulates. Thanks for setting me on a better path, and I appreciate your help! Not sure if I should mark this thread as solved? Again, thanks for the help. PWM