Turning weekly column data into months

[bowenuk]

Hi all,

Hoping someone very clever on here can help me with this problem.

We have a system at work that spits out a report in the following format:

Project	01/06/2015	08/06/2015	15/06/2015	etc
Project 1	100	200	100	400
Project 2	100	200	800	900
Project 3	150	200	300	500
etc.	...	...	...	...

We get this report on a weekly basis, so new columns appear on each new report. The date columns contain the revenue for that week, so would be split by 5 days to get a daily figure (or 4 days if there is a bank holiday).

I am trying to automate the process of turning the weekly columns into a total for each month. I need to be able to factor in that some weeks will be split across two months.

Unfortunately I can't change the structure of the report that we get from our system, but appreciate I may need to do some restructuring of the data each week - this is something I can try and automate with some VBA.

Any thoughts on how to tackle this would be greatly appreciated!

[oeldere]

Use the data of the days in your sheet.

After that re-arange your data in a table (verticaly way) e.g. with a macro.

After that use a pivot table to analyze the data.

[bowenuk]

Thank you for your response oeldere, please can you elaborate on this approach? I am not sure how to split the weeks where they cover two months?

[oeldere]

you get better help if you add an excel file.

You have to tell the forummembers what the criteria are.

If you can't tell us, how do you expect us to give you the good answer?

[protonLeah]

How to attach a Workbook
(please do not post pictures or links to worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"