Code for advanced selecting duplicates

**jmerch** · 2013-03-06, 01:21 PM

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.

Click image for larger version.

Name: 2013-03-05 Excel.jpg
Views: 145
Size: 64.9 KB
ID: 1179

**Bob Phillips** · 2013-03-06, 02:34 PM

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

**jmerch** · 2013-03-06, 02:47 PM

AttachedAttachment 1180

**Bob Phillips** · 2013-03-07, 08:19 AM

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** · 2013-03-07, 01:06 PM

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!