Macro to create chart

[k3vsmith]

I have a report (see BEI Report_test.xls)
I need to create a macro that would build a Sums worksheet which would in turn build a Chart worksheet (see BEI Report_test_Chart.xls)
Attached is an example of the kind of chart I'm looking for (Chart example.docx)

To create the sums worksheet, I need assistance.

I think what needs to occur is on the BEI Report worksheet I need to create a new column for each start/Finish so that it reads mm-yyyy. Or reformat the existing dates in all the start/finishes to show as mm-yyyy.
Then on the sums worksheet, list all dates (mm-yyyy) in order across row 2 and row 11 that are available in BEI Report from earliest to latest.

Then somehow show the sums on each. This will help build the chart. See Sums.docx for what I was thinking.

Need help please.

 

[k3vsmith]

Other attachments...

 

[Hui]

I have a non-vba, Non-Formulaic approach which unfortunately is only available in Excel 2013 and 2016

I hope you can follow

1. Adjust the Column layout and Make the Table a Table using , Insert Table


2. You will need Excel 2013/16 for this step
Select the Table
Goto the Data, From Table function

3. Select the First 4 Columns and then got the Transform, Unpivot Tab


Select Unpivot Other Columns

4. Excel will rearrange the data as:



Then goto the Home, Close and Unload

Continued next post:

 

[Hui]

5. You will now have a new Tab in Excel with your data
There will be 6 times as many rows as before, thats ok



Note there are six entries for every Activity ID and there are 2 new fields Date and Value, I Renamed the Field Date

6. Now add a PT based on this data




7. Now right click on any date at the Top and select Group
Group by Day
Then Format the field as d-mm or d-mmm

8. Enjoy

This uses the technique Chandoo described a few weeks ago at:
http://chandoo.org/wp/2016/03/08/unpivot-and-pivot-pq/

See your revised file attached:

 

[k3vsmith]

Thanks Hui. Thats a new feature I was unaware of. Unfortunately we are all on 2010. But with that said you did give me an idea on how to get this to work. I will also have to code this into a macro as this report is ran by many users at different times. I think you gave me enough info though that I can take it from here. Thanks!