Code for advanced selecting duplicates

jmerch

New member
Joined
Feb 14, 2012
Messages
32
Reaction score
0
Points
0
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.2013-03-05 Excel.jpg
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Can you post your workbook, as it stands now, with that current functionality?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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

New member
Joined
Feb 14, 2012
Messages
32
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Did you array-enter it? It is an array formula.
 

jmerch

New member
Joined
Feb 14, 2012
Messages
32
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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.
 
Last edited:

jmerch

New member
Joined
Feb 14, 2012
Messages
32
Reaction score
0
Points
0
Mind -> Blown :) thank you. Now I notice my hours aren't adding up so that's my next venture to look into.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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&"*")
 

jmerch

New member
Joined
Feb 14, 2012
Messages
32
Reaction score
0
Points
0
I caught the C12 and C25 formulas (I assume you meant column C, not B). And I like your idea of the * wildcard, which is kind of what sparked my initial want of the misc descriptions at the bottom.
 

jmerch

New member
Joined
Feb 14, 2012
Messages
32
Reaction score
0
Points
0
Hey Bob, this isn't completely working. There's Work Description fields that have hours assigned to them on the Data tab but they don't come in with your formula as a MISC description. Any ideas? Try putting 1141000 in the job number and running it. There is a work description used called Cleanup - service setup that doesn't come through.
 
Top