LinkBack URL
About LinkBacks
Example Attached.
Looking to combine the weekly data into 1 column by Month. This is apart of a larger solution and needs to be a macro.
Example Attached.
Looking to combine the weekly data into 1 column by Month. This is apart of a larger solution and needs to be a macro.
Hi hfechno,
In newer versions of Excel there is a tool called Power Query (Get & Transform in 2016) which can do this problem. A tool inside power query is UNPIVOT which I used on your data to give sheet 2 and the result you wanted using a Pivot Table. Pivot Tables have the ability to group your data by date using day, month, quarter or year. See the attached for my answer. Time to upgrade to a newer version of Excel?
Unpivot using Power Query then Group Dates in Columns.xls
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
I am currently using Excel 2013.
Any other suggestions?
Power Query, which is an Add In to 2010 and 2013 Excel is available for free.
https://www.microsoft.com/en-us/down...1-e5d9b3373a30
Hftechno - please update your forum profile with your current version of Excel. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Re Your PM
If you want it vba.
Result is slightly different.
Please Login or Register to view this content.
jindon, thanks so much for the reply and help. Yes this is exactly what I am looking for. Yes there is a calculation mismatch for May and June.
Perhaps someone can figure why (image attached). This is definitely the direction I am looking to take.
You need to tell us how to calculate each month.
Sorry for any confusion, I see the need for clarification and have submitted another sheet with random data between 1-5. I have also hidden all of the columns not apart of the issue.
On Sheet1 you will see all of the weeks broken down in RED. The column headers for the weeks are as such...
Example: We want to SUM the month of April. We have the following columns: W 04/01/18 - 04/07/18 W 04/08/18 - 04/14/18 W 04/15/18 - 04/21/18 W 04/22/18 - 04/28/18 W 04/29/18 - 05/05/18
IF "W 04" to start the column THEN SUM the row
The Column Header for the newly summed data will be "4" as in the fourth month (previously I state the header as APR)
Everything in RED is deleted and the only data that remains are the gray SUM columns.
hopefully this helps.
jindon, after retesting your code. My apologies, everything looks 100% !!!!!!!!!!
The only adjustment if possible would be:
The Column Header for the newly summed data will be "4" as in the fourth month (previously I state the header as APR)
Change
toPlease Login or Register to view this content.
Please Login or Register to view this content.
Thank you so very much!
tried to give you a reputation point... but could not since you got one last week from me.
but well deserved again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks