Allocating costs - one row to mulitple rows

Hi there
I work in the finance department and we receive an excel import file from payroll each fortnight which contains the payroll information assigned to overhead cost centres by salary expense code by employee code. Each employee could have up to 20 rows of information depending on the terms and conditions of their contract.
We would like to allocate these costs to products by employee within an Excel spreadsheet. We have a breakdown of each employee and the products they spend their time on (in percentages adding to a total of 100%) This is stored in a separate spreadsheet which can be copied into the payroll import file.
The payroll file contains the following information:
Payroll number, account code (including the overhead code and the expenditure code), the amount of the salary item, debit/credit indicator and the employee number.
I would like to create a macro which will allocate the costs charged to the overhead code to the product codes based on the employee split. This needs to be done by expenditure code as well
So for example
Employee 23 works in Team A and has Salary, Pension and First Aid Allowance information (in 3 separate rows in the payroll file).
Employee 23 works on 3 Products (Product 1, Product 2 and Product 3). The percentage splits are kept in a separate worksheet.
I would like the costs for Employee 23 to be spread across the 3 products - based on the percentage split.
The result would be 9 rows created in a separate worksheet with the account code reflecting the product account codes and the salary expenditure code - please see the attached spreadsheet which demonstrates what the input and output should be.
We have about 300 employees so this action would be repeated up to 300 times (once for each employee.
Do you have any suggestions on how to achieve this.

Thanks in advance for your help

To be honest: with 300 employees this task would be better suited to a small Access db where you upload your input files into tables and get the result by a query which can be retrieved into Excel by through a Database connection.
It certainly can be done in Excel too using VBA unless you don't need the payroll breakdown (in your example output I only see the product account code 3 times each), only the allocation per product. In this case you could calculate the allocated amount per worker and product with a formula in the product sheet.
I could prepare an example in your workbook each way over the weekend if you like. Only in Excel 2003 but there should be no major difference.
Have fun
Richard

Thanks for your suggestion, Richard. I will have a look at Access today and see what I can get done.
Thanks again

Hi All,

I have just recently started working with an organisation who currently doesnt have a reporting tool on the monthly overheads on employees, whether it be PAYE or Consultants/Contractors.

All the other companies I have worked for, have had some software in place where this information was extracted from, however they do not have such systems in place here. I know there will initially be manual entry, however I need to maintain this report every month.

I just wanted to know if anyone as something similar they have completed in their organisations.

very good, keep it up