payroll entry for accounting system - moving data from horizontal format to vertical

I'm trying to improve our payroll data entry into our accounting system. I've been working with our payroll company on this, but I need to do it in Excel. Attached is a sample file of what I'm given and what I need the end result to look like. I removed employee names, which makes it a bit more complicated to follow.

On the "data" tab I have data in columns A-L that are provided by the payroll company. This includes employee ID numbers, names (deleted for this posting), a branch/division ID, their job title, wage amount, bonus, extra health benefit, FICA, and employer paid unemployment taxes.

I need to break out wages and taxes based on where people bill their time. In the "hrs" tab I have a list of all employees and how many hours are billed to each functional area. This was provided via an export from our time system. Using a formula that was provided from another post on this forum I am able to break out the allocations. Percent of time per functional area is shown on the "data" tab in columns O-S, the corresponding wage/tax amounts are shown in the following columns. Columns U and V also show the employee's account number and region (these can be combined easily if needed).

I need to take this data and turn it into a vertical entry, as shown on the "GL" tab. Each person should have a line item dedicated to each functional area for wages and taxes. At a minimum, each employee will have two lines (one for wages, one for taxes). Some employees are billed to four different allocations, so they'll have eight lines (one for each wage allocation and one for each tax allocation). For example, the employee with ID number 113 (row 15) will need their data to come as four different rows:
10-5035-SRP, 561.74, Employee Name, PRO, PRO
10-5035-SRP, 1169.03, Employee Name, PRO, SIF
10-5105-SRP, 41.77, Employee Name, PRO, PRO
10-5105-SRP, 86.94, Employee Name, PRO, SIF
Note that they have PRO listed in all rows since that is the branch/division, then PRO is a separate allocation. The division needs to come over as well.

My current solution, which is time consuming, is to copy all of the cells so that each employee has all of the allocations listed with their name, including allocations without an amount. I copy all of the DEV, all of the PRO, SIF, GEN, etc. I can then sort out the zero allocation rows and delete them. I'm sure there is a better way to get the data from the format it currently is in to where I need it to end up.

I'm working with our payroll company, but unfortunately their program does not produce employee by employee entries, which is what I need.

Hopefully this all makes sense. Any help that can be provided would be great.

Thanks,
Adam

Hi,

all the above details are do complicated however have you tried vlookup or match formula??

Reg,
CVishu