HR Question on Organizing a file of employee hours

[Caitlin04198444]

CSV File:
Columns = last, first, EE name, paycode (several paycodes for each emp, ex John Smith will have a paycode for PTO, a paycode for jury duty, a paycode for OT, etc etc), Hours

My current process:
Isolate every single pay code than do a vlookup into a new file
Ex: filter by PTO, copy and paste, vlookup for the ee’s
Ex: filter by Jury duty pay, copy and paste and vlookup for the ee’s
Than I’m using the sum button to add the totals, than going back and checking 50x’s since it’s so manual it’s always off by a number

Goal:
1^st part: Columns with EE name, paycode than total hours
So instead of John Smith’s name listed 40 times for every paycode I want just one line with John Smith, 40 hrs PTO, 30 hrs OT, etc

2^nd part: A summary with the total hours worked for all ee’s (all OT, all PTO etc) totaled
I tried several different pivot table selections but I’m stuck. So what I’m doing is copying and pasting the numbers in. Shouldn’t there be a way when I update the first spreadsheet it automatically updates my final summary spreadsheet with the total numbers?

*My Excel skills are vlookup, pivot tables and cleaning up data (fyi when explaining lol)