Calculation Based on Duplicates

[rroybal]

Hopefully I can explain this correctly...I have a report that lists employees and their hours. Some employees are duplicates because they worked hours under a different title (See Employee G and M). I need to combine the hours of those duplicate employees except those that are listed with a title "5555 Vacation-Union" (Basically Employee G) and put it into Calculated Hours column. It would also be beneficial to have the second instance of hours zeroed out after the calculation. So I14 and I21 equal 0 and I13 =sum of H13 and H14, I20=H20.

 

[vw412]

The picture of the sheet does not seem to match your description. Could you edit to make them consistent?

 

[rroybal]

Apologies. I removed rows after I wrote the explanation. Updated below.
I have a report that lists employees and their hours. Some employees are duplicates because they worked hours under a different title (See Employee G and M). I need to combine the hours of those duplicate employees except those that are listed with a title "5555 Vacation-Union" (Basically Employee G) and put it into Calculated Hours column. It would also be beneficial to have the second instance of hours zeroed out after the calculation. So I9 and I16 equal 0 and I8 =sum of H8 and H9, I15=H15.

 

[vw412]

That makes more sense. Will the duplicates always be together? Do you sort the sheet by ID so all duplicates are together? Can there be more than 2 duplicates? How tdo you want to trigger this combining process?

 

[rroybal]

The duplicates will be together. I do sort by ID. There may be instances where there could be multiple duplicates. As for instances the highest I have seen is 4 instances of time where vacation time may not be the only Title. There is a COVID title as well. So an employee may have Journeyman time, Foreman time, Vacation time, and Covid time. Although it is a rare occurrence. The trigger would be if there are duplicates, only calculate the ones where the Title is not "5555 Vacation-Union" or "Covid" or whatever other title that may crop up that is non billable time. I have 700ish employees to sort through.

 

[vw412]

By "trigger" I meant what will you want to do to initiate the duplicate combining process, such as a CommandButton click or some other event.

 

[rroybal]

Hmmm...open to suggestions. These are done monthly, so I copy the new data into my "original" blank spreadsheet and then do a save as. It would be nice to have it just automatically update. So maybe as the data is pasted in?

 

[vw412]

If you do this process one a month every month you should have to whole process automated as much as possible. What is the source for the data that you copy into the "original" blank spreadsheet? Is it reasonable to try to automate the copy process? If so, the update you want to have happen based on your OP can be part of that copy process VBA.

 

[rroybal]

The source is from a .csv file from our accounting system. There is some manipulation of the source .csv file before it is ready to go into the monthly reporting listed above. I am trying to calculate the total hours worked per month and highlight/count if anyone averages 70-79 hours or 80 plus hours. I do not have administrative access to the section of the accounting software, hence the .csv file. Once I can get the "true" monthly hours for the employees, I can get the rest of the workbook to work correctly.