Code for advanced selecting duplicates

[jmerch]

OK, Here's what I got. We do our timesheets in excel. I made a Job Log spreadsheet that would pull the hours and work description from all our timesheets so I can see a breakdown of each work description. So on my Job Log, you enter the job number and it will show the hours for a set list of work descriptions. Problem is some users use to overwrite their work descriptions. So what I want to do is when the job number is entered and the hours are populated for the set list of work descriptions, I want any non-typical work descriptions to also populate and list the hours.

The work descriptions are in a named range called Work_Desc on a separate worksheet. Attached is a picture of what I have so far, I would like rows inserted between row 26 and 27 with the non-typical work descriptions and hours. At the same time it needs to only list a work description once if it's duplicated.

So if a user had "My Meeting" as a work description, it should insert between Rows 26 & 27 listing the description and total hours for that description. What I currently do now to tally hours for multiple instances of description is the formula

=SUMIFS(Reg_Hours,Job_Num,$C$2,Work_Desc,$B$9)

Is there an easy way with VBA? Or is there a simpler way? One thing I did test was to copy the Work_Desc range from it's sheet and paste at the bottom of this one, then use remove duplicates. I could do that and copy the formula down but I would rather this be automated if possible.

Any feedback appreciated.

[Bob Phillips]

Can you post your workbook, as it stands now, with that current functionality?

[jmerch]

AttachedAttachment 1180

[Bob Phillips]

Add this array formula to B28 and copy down

=IFERROR(INDEX(Data!$E$1:$E$1000,SMALL(IF((Data!$A$1:$A$1000=$C$2)*(NOT(ISNUMBER(MATCH(Data!$E$1:$E$1000,$B$9:$B27,0)))),ROW(Data!$A$1:$A$1000)),ROW(A1))),"")

You SUMIFS formulae can stay.

[jmerch]

What is this doing? I copied it and it turns the cell blank. I notice it's referencing E1:E1000 from the Data tab but that necessarily wouldn't be the case b/c I want it to only narrow down descriptions for the specified job number, right? I just want any work description from the Data tab that does not match the preset ones on the Job Log tab to appear with the hours.

Thanks!

[Bob Phillips]

Did you array-enter it? It is an array formula.

[jmerch]

I thought i entered it right. How do you array-enter it? I highlighted B28:B33 and then entered it. But I'm not familiar with arrary-enter.

[Bob Phillips]

Don't select B28:B33, just B28.

Add the formula, then hit Ctrl-Shift-Enter together, not just Enter.

Fill-copy B28 down to B33.

[jmerch]

Mind -> Blown thank you. Now I notice my hours aren't adding up so that's my next venture to look into.

[Bob Phillips]

Take a look at the formula in B12 and B25!

I also think B16 should be =SUMIFS(Reg_Hours,Job_Num,$C$2,Work_Desc,$B$16&"*")