Insert row after duplicates in a column, sum values in a seperate column, delete values in first column (sounds easy, right?)

[iamconstance]

Employee number	Employee name	Project	Hours
12345	John Smith	ABC	3
12345	John Smith	ABC	10
12345	John Smith	ABC	12

<tbody>
</tbody>

Hi All.

Hoping someone who knows VBA can help me out.

I have a spreadsheet that pulls employee staff numbers and the amount of hours they worked each week. The issue is that some employees may have worked on more than one project each week.

What I need to do is write some VBA that will identify the employee numbers that are duplicated (or triplicated, etc.), insert a row below them, copy the employee number and sum the total hours worked. I then want the duplicated (or triplicated, etc) employee numbers deleted so that I can do a vlookup and only pull back the total hours worked.

Example above

Any help is GREATLY APPRECIATED

 

[Joe4]

Are these values already sorted so all duplicates will appear in succession?
Is your data really structured as shown above, and is it in columns A-D?
Does your data start on row 2, and is there anything after the last row of data (any sort of totals or other data)?

 

[iamconstance]

1. I can sort the data so that it appears in succession, by sorting by employee number.
2. No, it's not exactly as above, there are columns in between, but I can figure out how to delete or discard those I don't need
3. Yes, data starts on row 2, and I can remove any subtotal rows.

 

[Blunder1]

I would just use a pivot table, the lookup into that

 

[iamconstance]

that's a great idea you know. I have never done a lookup in a pivot table. Off to google I go!