Consolidating data from days to months

[dalewms2]

Is there a way to extract data from an adjacent Excel worksheet (Daily) for one month and consolidate it to another worksheet (monthly)? Please see attached sheet. Thanks . If another way works better than Pivot Tables that is fine.

[benishiryo]

hi there. you have more than 1 year inside. does the year not matter? if it does, then it's better to type like 1Jan2014 in MONTHLY sheet B1. you can always format cells -> Custom:
mmm

what you can do in your DAILY 2 sheet in the pivot is to right-click A4 or any other dates -> Group. you can choose either just Months if Years doesn't matter. if it does, choose Years too. shift Date to the Column Labels and you have what you want in MONTHLY sheet.

if you want formulas in MONTHLY sheet (without years), try this in B2:
=SUMPRODUCT((TEXT(DAILY!$A$2:$A$2923,"mmm")=B1)*DAILY!$B$2:$B$2923)
do take note that B1 has an extra space behind "Jan", so it won't work. get rid of the space

with years (you have to type 1jan2014 like i suggested), try:
=SUMIFS(DAILY!$B:$B,DAILY!$A:$A,">="&B1,DAILY!$A:$A,"<="&EOMONTH(B1,0))

[mongoose36]

Try this...

TEST.xlsm

The file has a macro which will do what you are wanting to do I believe. You will need to enable macros. Once in click the button on the monthly sheet that says "Run Macro"

The code is below:

Please Login or Register  to view this content.

[dalewms2]

This works so awesome except the amount summed does not match the various months. For example, the macro returns the amount 2,021,438 for Hits January 2014 but it should be 446,601. Same is true for the other amounts. Thanks

[mongoose36]

That is because it is summing all Januaries irrespective of year. I didn't know you wanted only 2014.

Change this line:

Please Login or Register  to view this content.

To this...

Please Login or Register  to view this content.

BTW the way for this to work I changed you Month titles formatting from General to a custom formatting mmm, which essentially displays the Month only of any date entered. E.g. for Jan the cell contains 1/1/14...you could enter any day in January 2014 and the cell would still only show Jan. You could use this same technique if you wanted to keep other years separated.

I would then rename your Month tab to 2014. If you do this though you will need to change the following line:

Please Login or Register  to view this content.

To this...

Please Login or Register  to view this content.

And so on for all your years

[dalewms2]

I tried to make the changes. Obviously I am doing something wrong. I would like to keep all the years on the same worksheet. I attached what I did.

[mongoose36]

TEST2.xlsm

I corrected the vba...

[dalewms2]

It works perfectly except it only updates Jan 2014. I experimented with changing the Monthly.Range but it didn't work.

[mongoose36]

[It only updates Jan 2014 because in your daily tab 2014 has only Jan data. You will need more data for 2014 for more months to be calculated.

Try this file. I made the other months contingent on the date in Jan. This should allow you to change the year in Jan and the years will change for the others. then rerun the code and it will calculate for that year.

ATTACH]296890[/ATTACH]

[dalewms2]

Spectacular. Thank you for your help!!

[dalewms2]

Can you tell me how to copy this macro to an existing workbook? I have tried numerous times. I tried copying by by dragging the module to the destination workbook but received the following error message: 'Name conflicts with existing module, project or object library'. I tried copying and pasting also.

[mongoose36]

It's because the module name is probably 'Module1" and you already have a "Module1" in your existing workbook. Delete your module 1 and then drag and drop the one from the test.

[dalewms2]

Can you tell me how you would change the macro to select by rows instead of by columns as you did in this case?

[mongoose36]

What exactly do you want to do with the data?

[dalewms2]

I am just experimenting with the data.