+ Reply to Thread
Results 1 to 4 of 4

Automate Merge Weekly time-data into consolidated monthly (variable data structure)

  1. #1
    Registered User
    Join Date
    12-04-2021
    Location
    Casablanca
    MS-Off Ver
    Office Prof 2019
    Posts
    2

    Automate Merge Weekly time-data into consolidated monthly (variable data structure)

    With both apologies and thanks in advance for assistance on this challenge:
    I would like to understand an automatable approach to merging weekly time data from project time-tracking software (exported in CSV) into a consolidated monthly analytical sheet. By project/sub-project and date. As I am self-taught in Excel (and all things computer), background reading here and broadly gave me some ideas but I lack the real insights to successfuly work through - at least not without many days of bashing away. Hoping with the sample here I can short-cut to an approach (and understanding).

    By way of background explanation: The purpose is a self-reporting to management and colleagues (I am allowed to use my own tool to time track in a way I like but I need to give them reporting on a monthly basis on man-days, etc). I previously used another tool for time tracking that gave me a convenient monthly table that didn't require any extra work, but it is end-of-life and not cloud based so my tracking only was on the single computer, and I couldn't easily go from home to work laptop in tracking. So moved to try this month a new cloud based time tracking app.

    The core challenges are
    1. the new cloud tool I just started using only exports on a weekly basis rather than a monthly, so I have to collate weekly reports (CSV export) into monthly
    2. monthly collating is complicated as the sub-tasks / projects vary week to week (with new additions from time to time) so that the project listing output is not identical from week to week
    3. automating in part or whole to avoid stupidly spending hours on hand collating with the potential for manual error.

    Excel version I am working with is Excel 2019 (Office Pro suite) - and at work Excel 2016 (pro suite also) [for precision although I think there are not meaningful differences between the two editions

    Attached is sample sheet (from real but anonymised data, kept to 35 rows although real data set is 120 odd) where I have already combined the weekly exports into a single file by hand in a quick way, which ends up having different rows for different weeks, as what I did as simply copy over and use simple sort to organise by project name, so I end up with four rows for the same single month's work on a project in cases where I worked on such project/task every week. Which is messy and not ideal by far. The original CSVs are 7 day weeks.

    A sample original single week export is in 2nd excel as example.

    What I would ideally like to do is automate an approach to merge the month's worth of weekly exports, which would merge the CSV by
    1. Putting the date columns in date order (automatically rather than by hand as I have done now)
    2. Combine the rows that are the same project (same project name: this is standardised)

    After reading in forum on data merging (and elsewhere) I suspect that Power Query would be an effective solution, but I was not able to succesfully work through with my limited understanding of the tool and the limitations of self-taught-by-doing command of Excel (and this apologies to forum experts who may ID an obvious solution already discussed)

    Looking forward to any hand-holding and guidance.

  2. #2
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,278

    Re: Automate Merge Weekly time-data into consolidated monthly (variable data structure)

    Maybe so
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-04-2021
    Location
    Casablanca
    MS-Off Ver
    Office Prof 2019
    Posts
    2

    Re: Automate Merge Weekly time-data into consolidated monthly (variable data structure)

    Quote Originally Posted by Czeslaw View Post
    Maybe so
    Please Login or Register  to view this content.
    Thanks for the reply - I am afraid that I need a bit of hand holding here: I am not sure what this represents (I am guessing it coding for execution in Power Query but sorry I am not conversant...). With some modest explanation I can probably understand but need a bit of orientation.

    Otherwise, from the example attached I believe I may not have successfully conveyed my objective which is my fault, as my samples were in retrospect not well chosen.

    I am attaching a anonymised and reduced set of the original excels showing how the weekly data is organised on output (it is more or less the same data as the original regrouped Excel I shared, just sharing to show the originating set of export).

    I hope to be able to automate a process to combine the set of weekly exports and ideally combined the rows that are of the same Project (example "XCo.CapRaise" has five rows but really it's one thing(project/tasks) and the rows are an artefact of the differing weeks (so the column header differs of course by the dates) so that there is one row for the month's data for that specific project (although the rows grouped by project/task unique name is acceptable in a way, but messy)

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Automate Merge Weekly time-data into consolidated monthly (variable data structure)

    Have a look here:

    https://support.microsoft.com/en-us/...8-6a00041c90e4

    and here:

    https://chandoo.org/wp/combine-excel...g-power-query/

    There are many more tutorials online. See if you can work through it and shout if you get unstuck. PQ is a fairly steep learning curve best learnt hands on, but it's worth the climb.
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel 2007 : How to convert weekly data to monthly data
    By kissanbhai009 in forum Excel General
    Replies: 3
    Last Post: 08-10-2017, 04:35 AM
  2. Formula to convert monthly data into weekly data - help needed
    By dobrica3 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-12-2016, 10:00 AM
  3. Converting weekly to monthly data and Yearly data to monthly
    By MilicaMatovic in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 01-29-2016, 09:48 AM
  4. Replies: 0
    Last Post: 02-04-2015, 04:24 AM
  5. [SOLVED] over consolidated data - need dissection and structure help!
    By shootstill in forum Excel General
    Replies: 7
    Last Post: 04-16-2014, 12:54 PM
  6. Consolidated Monthly view of weekly cost and labour
    By audiman09 in forum Excel General
    Replies: 3
    Last Post: 02-05-2013, 12:29 AM
  7. Replies: 1
    Last Post: 04-17-2009, 04:57 AM

Tags for this Thread

Bookmarks

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