Recurring projects in a week

  • I am trying to make a timesheet for my job, and have it add the hours worked for each project. I have something like the table below:[TABLE="width: 500"]

    [tr]


    [td]

    DAY

    [/td]


    [td]

    HOURS WORKED

    [/td]


    [td]

    PROJECT

    [/td]


    [/tr]


    [tr]


    [td]

    monday

    [/td]


    [td]

    8

    [/td]


    [td]

    project a

    [/td]


    [/tr]


    [tr]


    [td]

    tuesday

    [/td]


    [td]

    8

    [/td]


    [td]

    project b

    [/td]


    [/tr]


    [tr]


    [td]

    wednesday

    [/td]


    [td]

    8

    [/td]


    [td]

    project b

    [/td]


    [/tr]


    [tr]


    [td]

    thursday

    [/td]


    [td]

    8

    [/td]


    [td]

    project a

    [/td]


    [/tr]


    [tr]


    [td]

    friday

    [/td]


    [td]

    8

    [/td]


    [td]

    project c

    [/td]


    [/tr]


    [tr]


    [td]

    saturday

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    sunday

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]
    (sometimes we work saturday and sunday)
    and I'm trying to get vba to spit out in a cell below something like the following:


    [TABLE="width: 500"]

    [tr]


    [td]

    Project a (16), project b, (16), project c (8)

    [/td]


    [/tr]


    [/TABLE]


    I've tried to go down the PROJECT column and if the project name repeats, then it adds the hours worked. But when I do that, I get something like Project a (16), Project b(16), Project b(16), Project a (16), project c (8)


    Does anyone have any ideas that could help?
    I'm sorry if it sounds confusing.

  • Re: Recurring projects in a week


    I think this can be done with an array fairly easily, just don't have the time to give it right now. I can do it with if/isna/match statements, but that is a little longer for the input. If you want that I can give it. Not sure you need vba unless you just want it. Either way I'll check back later.

  • Re: Recurring projects in a week


    I've tried making arrays, but like I said, I get to:


    Project a (16), Project b(16), Project b(16), Project a (16), project c (8)


    I need to eliminate recurring projects, so it looks like:


    Project a (16), Project b(16), project c (8)

  • Re: Recurring projects in a week


    Here's what you can enter for just one cell, i.e. project a... will have to redo for other projects, but will sum all of that type. =SUM((IF(ISNA((IF((MATCH("project a",$C2,0))=1,B2,0))),0,B2)),(IF(ISNA((IF((MATCH("project a",$C3,0))=1,B3,0))),0,B3)),(IF(ISNA((IF((MATCH("project a",$C4,0))=1,B4,0))),0,B4)),(IF(ISNA((IF((MATCH("project a",$C5,0))=1,B5,0))),0,B5)),(IF(ISNA((IF((MATCH("project a",$C6,0))=1,B6,0))),0,B6)),(IF(ISNA((IF((MATCH("project a",$C7,0))=1,B7,0))),0,B7)),(IF(ISNA((IF((MATCH("project a",$C8,0))=1,B8,0))),0,B8))) since this is repetitive, i'm sure it could be done much more cleanly with an array. Just not sure best way to write that.

  • Re: Recurring projects in a week


    Assuming Heading in A1:C1, data starts from row2.

Participate now!

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