Possible with VBA? Payroll allocation changes

M

mgerner

New Member
Joined
Jun 16, 2018
Messages
5
I'm not sure if this is even possible using VBA and it's hard to explain, but I'll give it a try. I’ve been trying to figure this out for a few weeks with no luck. Basically, I have two worksheets with different payroll related data. Below is some sample data. On sheet1, I have 4 columns showing that the individuals payroll is split 50/50 between "Account1" and "Account2.” Then, on sheet2, I have 3 columns. Column A has the individuals name, column B has the account, and column C has the correct % allocation that the individuals payroll needs to be split by.

The % allocations on sheet1 (50/50 b/w “Account-1” and “Account-2” in the example below) are incorrect. Sheet2 has the correct allocation (60/20/20) for the individual. So, what I have been having to manually do, is figure out the total $ amount for the individual for SALY, USM, and USS, and then re-splitting out that amount by the correct % allocations.

Is this is even possible using VBA?

[TABLE="width: 276"]
<colgroup><col width="125" style="width: 94pt; mso-width-source: userset; mso-width-alt: 4437;"> <col width="86" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3043;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2673;"> <col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2958;"> <tbody>[TR]
[TD="class: xl69, width: 369, bgcolor: transparent, colspan: 4"]Starting data on Sheet1[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]Name[/TD]
[TD="class: xl72, bgcolor: transparent"]Account[/TD]
[TD="class: xl72, bgcolor: transparent"]$ Amount[/TD]
[TD="class: xl72, bgcolor: transparent"]Allocation[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - SALY[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-1[/TD]
[TD="class: xl66, bgcolor: transparent"] 50.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - SALY[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-2[/TD]
[TD="class: xl66, bgcolor: transparent"] 50.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USM[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-1[/TD]
[TD="class: xl66, bgcolor: transparent"] 5.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USM[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-2[/TD]
[TD="class: xl66, bgcolor: transparent"] 5.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USS[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-1[/TD]
[TD="class: xl66, bgcolor: transparent"] 5.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USS[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-2[/TD]
[TD="class: xl66, bgcolor: transparent"] 5.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, colspan: 3"]Adjustments to be made on Sheet2[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]Name[/TD]
[TD="class: xl72, bgcolor: transparent"]Account[/TD]
[TD="class: xl72, bgcolor: transparent"]Allocation[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-1[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]60%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-2[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-3[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, colspan: 4"]Desired outcome on Sheet3[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]Name[/TD]
[TD="class: xl72, bgcolor: transparent"]Account[/TD]
[TD="class: xl72, bgcolor: transparent"]$ Amount[/TD]
[TD="class: xl72, bgcolor: transparent"]Allocation[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - SALY[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-1[/TD]
[TD="class: xl66, bgcolor: transparent"] 60.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]60%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - SALY[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-2[/TD]
[TD="class: xl66, bgcolor: transparent"] 20.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - SALY[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-3[/TD]
[TD="class: xl66, bgcolor: transparent"] 20.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USM[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-1[/TD]
[TD="class: xl66, bgcolor: transparent"] 6.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]60%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USM[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-2[/TD]
[TD="class: xl66, bgcolor: transparent"] 2.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USM[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-3[/TD]
[TD="class: xl66, bgcolor: transparent"] 2.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USS[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-1[/TD]
[TD="class: xl66, bgcolor: transparent"] 6.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]60%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USS[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-2[/TD]
[TD="class: xl66, bgcolor: transparent"] 2.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USS[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-3[/TD]
[TD="class: xl66, bgcolor: transparent"] 2.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20%[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
645
smozgur
smozgur
J
  • Question Question
Take Excel Worksheet, Divide into Worksheets/Workbooks by Employee/Card #, Email via Gmail to Employee for Input
Replies
9
Views
879
jennifer7i884
J
Rob_010101
  • Question Question
Filter Data?
2
Replies
16
Views
1K
kevin9999
K
O
  • Question Question
Fill in cells based off VBA User Form input
Replies
8
Views
1K
DanteAmor
DanteAmor
W
  • Question Question
Personnel Roster using Macro's
Replies
0
Views
332
womeclr
W

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top