Transforming Data from Daily to Weekly

1. Transforming Data from Daily to Weekly

First post. Hope you can help as kindly as the community always does!

Im trying to change some data from a daily basis to a weekly basis , but I realized that the amount of data is so big that doing it mannualy is going to take forever. I attach here an example of how i am doing it right now. As you can see the in the Weekly Sheet, the Sum(B1:B5) and the Sum(B6:B10) and so forth represent the addition of the week BEFORE so the green highlighted days are Mondays that are NOT included in the sum.
Is there any way i can automate this process? It is very time consuming. I want the sum to add all the value of the past week (M-F) and show it in a cell that represents the date as monday of the following week.To make it clear:

Date Daily Value / Weekly Transformation
Mo1 2 / Mo2 2+3+4+4+7
Tu1 3 / Mo3 9+5+2+3+4
We1 4
Th1 4
Fr1 7
Mo2 9
Tu2 5
We2 2
Th2 3
Fr2 4
Mo3 1

Any ideas? I attach the spreadsheet if I did not make my point clear, its pretty hard for me to explain it.
Thanks in advance guys!
Sample Daily to Weekly.xlsx

2. Re: Transforming Data from Daily to Weekly

here are 2 ways to solve it

weekly contains the formula solution

wk contains the macro solution

3. Re: Transforming Data from Daily to Weekly

:O Thank you so much! Would you mind explaining me the command a little bit?
For example for week 1 its =IFERROR(SUM(INDIRECT("daily!B"&MATCH(\$A1,Daily!\$A:\$A,0)-5&":B"&MATCH(\$A1,Daily!\$A:\$A,0)-1)),0)

I guess I will need some reading on Match and IFERROR, because ive never used these two formulas before.
I realized there is a mistake with the formula. Could we stick with the formula solution? Im not very used to using macros ( I still have to learn a lot)

If you continue dragging the formula downwards the result does not equal to the sum of the past week. Attached you can find the file with the error. The are two sheets, one that calculates weekly values based on the formula and one based on the manual calculation. You will find a comment that shows where the formula makes mistakes (The Zeroes are a sign of mistakes).
Thanks again
Viva Guatemala =D
Sample Daily to Weekly.xlsm

4. Re: Transforming Data from Daily to Weekly

Hope anyone can help, doing this manually takes hours.
To make it clear, the formula =SUM(B3:B7) follows a pattern that i can't just drag down because the pattern is NOT =SUM(B4:B8) but it's =SUM(B8:B12).
The correct pattern should look like this.
1.=SUM(B3:B7)
2.=SUM(B8:B12)
3.=SUM(B13:B17)
4.=SUM(B18:B22)

Notice that both numbers increase by 5 every time. Ideas?

5. Re: Transforming Data from Daily to Weekly

With an pivot table.

See the attached file.

6. Re: Transforming Data from Daily to Weekly

Thank you ! I will study this method

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1