Pivot on one column per month.

[theBeachBoy]

I have a set of data where there is a start and end date for each activity.
With some formulas, I can put a score (points) for each month depending if it's the start month, a month between start & end, or an end month.

I am trying to get to my pivot table without having to repeat each line, once for each month between the start and end date.

If you look at the file, my raw data is in the first tab, my current process (via vba), generates the table in the second tab, where I can use a pivot table to get the table on the right with the correct numbers per month and project. The last tab has the same data but where I add only columns, resulting in a much smaller dataset but where I am unable to get a pivot table with the correct output like in the second tab.

My main problem with tab 2 is that I have 18,000 rows and over 30 columns, when I need to multiply each row by an average of 5, it takes a long tiem to run the macro and makes the dataset really big.
In the ats tab my dataset keeps the same number of rows and only adds a dozen columns... but I don't know how to get to the pivot table.

Anyone has any idea how to solve this?

See attached file:
Book1.xlsx

[cbatrody]

Hi,

See the attached file, I have modified the Pivot Table in 'EDIT1' tab based on the description provided by you. See if this is what you are looking for.

[theBeachBoy]

Hello, thanks for the file.

I think my description was misleading.

The original Data is the tab DATA
The tab EDIT1 is the current method, which is to add a line for each month between start and end (from current month to 12 months ahead). This allows me to make the pivot table that is in the same tab (EDIT1), which is exactly the output I need.
The tab EDIT2 is a table where the same original data is modified in order not to add any lines but simply have a column for each of the current and next 12 months. My problem is that I am not able to get a pivto table out of this data as the data is not "proper" with a line for each month.

I would like to have the same pivot table that is currently is EDIT1, but with the data in EDIT2, which both come from the tab DATA and has the same calculations.