Aggregate daily time sheets to weekly reporting

    Hi
    I have a spreadsheet which works but it is way too slow - what I'm trying to do is to aggregate daily time sheets by person and job, determining the unique combinations of people and jobs then adding the total values for those combinations by week number.
    I've used concatenate to determine the unique combinations and then nested if statements in an array sum formula to get the answer but each time it calculates it takes up to 5 minutes.
    I have around 6,000 initial daily records but have reduced that in a workbook which is attached.
    The workbook has 3 sheets, the first with name, job and values, the second with the same amount of rows concatenating the name and the job and providing numbering of unique combinations, and the third worksheet references the numbering and carries out the conditional array sum on the original data.
    I'm wondering if there is a more classical or VBA approach to get the DataTransfer worksheet result?
    Many thanks in advance.
    John

    Hi,


    Have you noticed that both your Named Ranges : User and Tittle do not include the very last record (i.e. row 231 ...)


    In terms of alternative, would you rather go for a UDF or a macro to run ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Carim
    Many thanks, yes I just picked that up.
    I'm wondering if a macro would be the way to go for speed and efficiency, the data is always in the same format.


    Thanks
    John

    Well ...


    The best solution is the one that matches your way of using your worksheet ...


    By the way ... before going any further ... have you tested a Pivot Table ... ???


    See attached example ... done in 10 seconds ...:wink:


    and, as a bonus ... you get the ' drill down ' feature with a double-click on any amount ... to get all the details ...

    Carim
    Yes thanks, unfortunately I have to transfer the data into another Workbook that compares actuals with forecasted weekly data, Pivots are great if this was a stand alone workbook.
    Many thanks
    John

    Understand your constraint ...


    But given the speed advantage the Pivot Table has over all the other solutions ... (array formulas, sumproduct, sumifs, etc...)


    May be you should consider using the Pivot table which can be refreshed very quickly to feed into whatever reporting format you are bound to stick to ...


    With this approach the pivot table would simply be your calculation engine ... in between your daily Time Sheet ... and your Report Sheet ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login