Formula Needed for Bonus Allocation

[kmyers1970]

I am trying to figure out a way to create a sheet for calculating bonus allocations from a sheet of data used to allocate payroll. Here is what the payroll sheet looks like:

<strike></strike>And the formulas I need would produce a list of employee ids and their entity allocation like this:

I am not limited to using formulas as I think I need a VBA macro to accomplish this. Any help would be greatly appreciated.


<strike></strike>EDIT:
I already messed up allocation screenshot - cell C2 and C3 should read 8% and 9%.

 

[Eric W]

Welcome to the forum.

I suspect that you're right, a VBA macro is probably the best way to go. In fact, I probably could have written one faster than I worked out these formulas!

Nevertheless, these work:

	A	B	C
1	ID#	Entity Code	Alloc
2	12025	Entity2	8%
3	12025	Entity3	9%
4	12025	Entity4	10%
5	12806	Entity4	100%
6	12342	Entity6	14%
7	12342	Entity7	9%
8	12342	Entity8	3%
9	12342	Entity9	1%
10	11939	Entity1	20%
11	11939	Entity5	20%
12	12375	Entity2	50%
13	12375	Entity4	50%
14

<tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula A2 =IFERROR(INDEX(Sheet6!$A$2:$A$7,MATCH(A1,Sheet6!$A$2:$A$7,0)+(COUNTA(OFFSET(Sheet6!$D$2:$L$2,0,0,MATCH(A1,Sheet6!$A$2:$A$7,0)))<=ROW()-2)),"") C2 =IF(A2="","",INDEX(Sheet6!$D$2:$L$7,MATCH(A2,Sheet6!$A$2:$A$7,0),MATCH(B2,Sheet6!$D$1:$L$1,0))) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula B2 {=IF(A2="","",INDEX(Sheet6!$D$1:$L$1,0,SMALL(IF(INDEX(Sheet6!$D$2:$L$7,MATCH(A2,Sheet6!$A$2:$A$7,0),0)<>"",COLUMN($D$2:$L$2)),COUNTIF($A$2:$A2,A2))-COLUMN($D2)+1))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

If you still want the macro, let me know and I'll work something up.


Edit: I also just noticed that the formulas go awry if some employee has no numbers for entities 1-9.

 

[frank_AL]

Other more experienced Excel SMEs might be able to provide you a Formula driven solution.
Below is my VBA based solution that worked as expected in my test file built from your screenshots.

Give it a try and let me know if it works okay for you.

Sub BonusAllocation()
Dim i As Long
Dim x As Long
Dim lr As Long
Dim CFws As Worksheet
Dim CTws As Worksheet


Set CFws = Sheets("Sheet1")
Set CTws = Sheets("Sheet2")


cflr = CFws.Cells(Rows.Count, "A").End(xlUp).Row
ctlr = CTws.Cells(Rows.Count, "A").End(xlUp).Row + 1


For i = 3 To cflr
    For x = 4 To 12
        If CFws.Cells(i, x) > 0 Then
            CFws.Cells(i, 1).Copy Destination:=CTws.Cells(ctlr, 1)
            CFws.Cells(1, x).Copy Destination:=CTws.Cells(ctlr, 2)
            CFws.Cells(i, x).Copy Destination:=CTws.Cells(ctlr, 3)
            ctlr = ctlr + 1
        End If
    Next x
Next i


End Sub

 

[kmyers1970]

Hi Eric,
Thank you for the quick reply. I'm having a little trouble getting the formulas to work with my data. The one big difference is my actual sheet from the finance payroll goes from columns A1:CU213 and doesn't end at column L like my sample sheet. The other item I failed to mention is the employee id can appear on multiple rows - they have a grouping column which I don't need but the end result is I could have employeeid 123 on row 10 and row 50 with a percentage allocation for the same entity - 1 row may have 30% and the other row 70%. The weird part is the formula doesn't blow up until it gets to row 13 which is where the sample data ends at row 13 so I must be messing something up in my version.

Here are my formulas:
A1: =IFERROR(INDEX('PR SPREAD'!$A$2:$A$212,MATCH($A1,'PR SPREAD'!$A$2:$A$212,0)+(COUNTA(OFFSET('PR SPREAD'!$D$2:$CU$2,0,0,MATCH($A1,'PR SPREAD'!$A$2:$A$212,0)))<=ROW()-2)),"")
B1: =IF(A2="","",INDEX('PR SPREAD'!$D$1:$CU$1,0,SMALL(IF(INDEX('PR SPREAD'!$D$2:$CU$212,MATCH(A2,'PR SPREAD'!$A$2:$A$212,0),0)<>"",COLUMN('PR SPREAD'!$D$2:$CU$2)),COUNTIF($A$2:$A2,A2))-COLUMN('PR SPREAD'!$D2)+1))
C1: =IF(A2="","",INDEX('PR SPREAD'!$D$2:$CU$242,MATCH(A2,'PR SPREAD'!$A$2:$A$212,0),MATCH(B2,'PR SPREAD'!$D$1:$CU$1,0)))

And I do the ctrl-shift-enter for B1 and when I copy the formula down and hit row 13, I start to get errors. I'm also not sure why employee 11939 shows multiple rows. Originally, this employee id was listed twice with 70% and 30% for the same entity but in 2 different rows so I changed the data to be 100% and deleted the duplicate row but the duplicate data remains with the formulas.

I am going bonkers trying to figure this out!

 

[frank_AL]

kmyers 1970

I updated my Macro to allow it to identify the last column so that it works regardless of how many columns you might have.

Give it a try, the VBA code sure seems easier to develop for your desired solution than the complicated Formula you are trying to reconcile.

Sub BonusAllocation()
Dim i As Long
Dim x As Long
Dim lr As Long
Dim CFws As Worksheet
Dim CTws As Worksheet


Set CFws = Sheets("Sheet1")  '  Copy From Worksheet
Set CTws = Sheets("Sheet2")  '  Copy To Worksheet


cflr = CFws.Cells(Rows.Count, "A").End(xlUp).Row  '  Copy From Last Row
cflc = CFws.Cells(1, Columns.Count).End(xlToLeft).Column  '  Copy From Last Column
ctlr = CTws.Cells(Rows.Count, "A").End(xlUp).Row + 1  '  Copy To Last Row


For i = 3 To cflr
    For x = 4 To cflc
        If CFws.Cells(i, x) > 0 Then
            CFws.Cells(i, 1).Copy Destination:=CTws.Cells(ctlr, 1)
            CFws.Cells(1, x).Copy Destination:=CTws.Cells(ctlr, 2)
            CFws.Cells(i, x).Copy Destination:=CTws.Cells(ctlr, 3)
            ctlr = ctlr + 1
        End If
    Next x
Next i


End Sub

 

[kmyers1970]

Frank_AL,
I think I totally agree with you! My eyes are going cross-eyed trying to figure the formula route out. I did make some tweaks to your VBA code last night and it has been working quite well. Thanks for your assistance.

 

[frank_AL]

Thanks for letting me know. If you don't mind, share your updated code if it was anything more than changing the Worksheet names.
It would be helpful to know what changes were needed for my own continuing journey of learning VBA.

 

[kmyers1970]

Sure thing. I needed to delete any rows in the source sheet that had a blank employee id, then clear the contents of the destination tab before rebuilding it. I also included a formula in the destination sheet for column D. Here is what I am using:
Sub BonusAllocation()
Dim i As Long
Dim x As Long
Dim lr As Long
Dim CFws As Worksheet
Dim CTws As Worksheet



Set CFws = Sheets("PR Spread")
Set CTws = Sheets("EMP-PROP-ALLOC")
'Delete rows with a blank employee id from PR Spread
On Error Resume Next
CFws.Columns("A").SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0

'Clear data from EMP-PROP-ALLOC before rebuilding it
CTws.Rows("2:" & CTws.Rows.Count).ClearContents

cflr = CFws.Cells(Rows.Count, "A").End(xlUp).Row 'Gather Copy From last row count
cflc = CFws.Cells(1, Columns.Count).End(xlToLeft).Column 'Gather last column count
ctlr = CTws.Cells(Rows.Count, "A").End(xlUp).Row + 1 'Copy to last row




For i = 3 To cflr
For x = 4 To cflc
If IsNumeric(CFws.Cells(i, x)) And CFws.Cells(i, x) > 0 Then
CFws.Cells(i, 1).Copy Destination:=CTws.Cells(ctlr, 1)
CFws.Cells(1, x).Copy Destination:=CTws.Cells(ctlr, 2)
CFws.Cells(i, x).Copy Destination:=CTws.Cells(ctlr, 3)
CTws.Range("D" & ctlr).Formula = "=IFERROR(VLOOKUP($B" & ctlr & ",'Prop Summ'!$A$20:$Z$150,26,FALSE),0)"
ctlr = ctlr + 1
End If
Next x
Next i


End Sub

Thanks again for your help.

 

[frank_AL]

Very good. Thanks for sharing.