Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Code for advanced selecting duplicates

  1. #1

    Code for advanced selecting duplicates



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

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

  3. #3
    AttachedAttachment 1180
    Attached Files Attached Files

  4. #4
    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.

  5. #5
    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!

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

  7. #7
    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.

  8. #8
    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 by Bob Phillips; 2013-03-07 at 03:24 PM.

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

  10. #10
    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&"*")

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •