Help with Splitting Charges to Cost Centres According to %

Hi Guys,

This is my first time in this form and hope to obtain some help with a perplexing Excel issue.

We have a number of staff working on different projects and consequently different cost centres. I am trying to split their payroll charges (for example basic pay, employer's NI contribution) to the respective cost centres. I am working with two spreadsheets:
1) Raw data (containing just the payroll data)
2) Spreadsheet recording the % split per cost centre

I am working with over 500 employee payroll data where at least 300 of them have splits. I have attached the spreadsheets.

Many thanks in advance for your help!

Victory

Are there only 3 cost centres?

hi John,

There are more than 3 cost centres but the splits are always a maximum of 3. Hope that made sense.

hi dflak,

Many thanks for your response! Brain's fried at the moment but will have a look at your solution. Thanks again for taking the time to reply-much appreciated

Do a lookup on the EE id; Sum the amounts to get Total for Each EE. See attached.

solution with code

Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet, lr1 As Long, lr2 As Long, x As Long, i As Long
Set ws1 = Sheets("% Split per cost centre")
Set ws2 = Sheets("Raw Data")
lr1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
lr2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
For x = 2 To lr2
    If ws2.Range("G" & x) = "" Then
        For i = 2 To lr1
            If ws2.Range("A" & x) & ws2.Range("B" & x) & ws2.Range("C" & x) = _
                ws1.Range("A" & i) & ws1.Range("B" & i) & ws1.Range("C" & i) Then
                mycol = Application.Match("Cost Center" & Chr(10) & ws1.Range("F" & i), Range("1:1"), 0)
                If IsError(mycol) Then
                    mycol = ws2.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Column
                    ws2.Cells(1, mycol) = "Cost Center" & Chr(10) & ws1.Range("F" & i)
                    ws2.Cells(1, mycol).Font.Size = 9
                    ws2.Cells(1, mycol).HorizontalAlignment = xlCenter
                End If
                ws2.Cells(x, mycol) = ws2.Range("F" & x) * ws1.Range("G" & i)
            End If
        Next
    End If
Next
End Sub

Kind regards
Leo

Hi Leo, thanks for taking the time to respond. I have no clue about coding but your help is appreciated nonetheless