VBA / Macro to Add Payroll Categories

J

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,557
Office Version
  1. 365
Platform
  1. Windows
Every week, I have to analyze ten workbooks like what I have attached. Right now, I have to manually add each category per job department so under job 606, I have to add all the amounts and hours worked for code HL1, then I have to add all amounts and hours for code REG, and so on. Is there some way I can get code to do all the calculations for me by looking at the job code and adding each category? At the end of the day, for each department number, I want to have a total for each category and here are all the possible categories:

- REG
- HL1
- PTO
- SCK
- OT
- DBT
- VCP
- FLT
- VAC

https://app.box.com/s/m8e0rut16m1j0308b6x1dxs2cj1bi9r4
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Hi,

Should the end result look like this, for the example given?

21975no.jpg
 
Upvote 0
Yes but there will be other job codes besides the ones in this workbook. Did you just do a subtotal?
 
Upvote 0
So.. for the given worksheet, this is correct.

Ok..

When it comes to the ten workbooks...

Is it a case of (for example): Workbook 1 contains Project Code 606... and Earnings Codes: HL1 and REG and Workbook 2 also contains Project code 606 .. and Earnings Codes: HL1 and maybe, say, PTO?

i.e. will the data combine together.. or do you never have a project code appear in more than one workbook (i.e. 606 will ONLY appear in workbook 1 and therefore NOT workbooks 2~10)??


Also, to be clear.. is there only one worksheet per workbook?
 
Upvote 0
Yes, that is correct and yes, only one worksheet per workbook.

On each workbook, each project code will appear only once on that page but each code could have any of the listed codes I gave above.
 
Upvote 0
On each workbook, each project code will appear only once on that page but each code could have any of the listed codes I gave above.
Click to expand...

Sorry if I wasn't clear... what I'm really asking is:

Q1: Will a Project Code appear more than once across workbooks?


i.e. could I find Project Code 606 in Workbook 1 AND Workbook 2 or 3... meaning the data would have to be combined between workbooks?



ADDENDUM


Q2: What are the Workbook names?

Is there a standard (e.g. every workbook starts with the name "Workbook" and then has a number...

giving

Workbook1.xls
Workbook2.xls
..
Workbook10.xls





Q3: Are the Workbooks all in the same directory?




Q4: and within each workbook, the single sheet - is it always given the same name.. or does it change for each workbook?
 
Last edited:
Upvote 0
My apologies! In my attempt to hide confidential employee information, I messed up some of the data and in the end, confused you and myself. Here is the actual workbook I get once per week (with names changed but everything else is the same):

https://app.box.com/s/m8e0rut16m1j0308b6x1dxs2cj1bi9r4

Now to answer your questions:

1) A project code will appear only twice in each workbook: once under the "Project Code" header and again at the bottom with "Total" added to the header
2) Workbooks are always extracted from a PDF so when converted to Excel, I am left with one tab and one workbook named Table 1
3) Since they are extracted from a PDF, the Excel workbooks are saved locally on my desktop (no shared drive or cloud)
4) Always the same name of Table 1

Hopefully, this is more clear than my previous posts and again, sorry for the confusion.
 
Upvote 0
So that you have something in the short-er term...

Excel 2010
ABCDEFGHIJKLMNOPQRSTU
Project CodeEarnings Code
606Smith, JohnHL1
REG
Total
Smith, JohnHL1
REG
Total
606 Total
Project CodeEarnings Code
700Smith, JohnHL1
REG
Total
Smith, JohnHL1

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Last, First MI[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: right"]Sum of Hours Paid[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Sum of Amount Paid[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]8.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]189.20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]31.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]733.15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]39.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]922.35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]8.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]156.32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]32.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]625.28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]40.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]781.60[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]79.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$1,703.95[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]Last, First MI[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: right"]Sum of Hours Paid[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Sum of Amount Paid[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]8.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]240.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]32.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]960.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]40.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1,200.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]8.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]224.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Table 1​




Excel 2010
WXYZ

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Project[/TD]
[TD="align: center"]Earnings[/TD]
[TD="align: center"]Hrs[/TD]
[TD="align: center"]Amt[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]606[/TD]
[TD="align: center"]HL1[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]189.2[/TD]

</tbody>
Table 1 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]W2[/TH]
[TD="align: left"]=IF(ISNUMBER(A2),IF(A2<>W1,A2,W1),W1)[/TD]
[/TR]
[TR]
[TH]X2[/TH]
[TD="align: left"]=IF(AND(LEN(K2)>1, LEN(K2)<4),K2,"")[/TD]
[/TR]
[TR]
[TH]Y2[/TH]
[TD="align: left"]=IF(AND(LEN(K2)>1, LEN(K2)<4),N2,"")[/TD]
[/TR]
[TR]
[TH]Z2[/TH]
[TD="align: left"]=IF(AND(LEN(K2)>1, LEN(K2)<4),R2,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Using your data in format you have provided.. putting these 4 formulae into the cells shown (W2, X2, Y2 and Z2).. then copying them all the way down for each row of your given data (in the Excel sheet, extracted from the PDF) will give you data that you can then simply create a Pivot Table from.


Creating the Pivot Table as follows:

11ltqjc.jpg


will automatically summarise the data as you want it to be.


NB: Having to do this ten times, one for each of your PDF extracts, is still time consuming so.. hopefully tomorrow.. I should have a spreadsheet that will do it at the click of a button... (ok.. maybe two button clicks!) which I will post a link to as soon as it's finished.

It would require you to simply have your ten PDF files... manually convert all ten to Excel files (as you do now) and save the ten (now Excel) files into an empty directory.

Then, you click on the first button.. select the directory with the ten new Excel files (which were PDF files)...

Then you click on a second button and it opens each in turn, copies the data, adds formula, creates a pivot table, closes the Excel (from PDF) file..
and does that for each of the ten.

When it's finished.. in around under a minute, as a guess-timate, you'll have ten completed summarised tables.. one for each of your original PDFs, each with the correct codes found in each PDF respectively.
 
Last edited:
Upvote 0
I appreciate all the effort you are putting into this for me!
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Looking to expand tool to track vacation pro-rations
Replies
1
Views
315
bh24524
B
P
  • Question Question
Subtotaling Unique Hours Worked Categories
Replies
1
Views
519
GuzmanServices
G
S
  • Question Question
Macro to add sum total to track percentages
Replies
0
Views
341
spenserallen7
S
K
  • Solved
Code to search job id from multiple sources and update main spreadsheet.
2
Replies
10
Views
681
mumps
mumps
L
  • Question Question
Step 2: VBA Macro to Sum
Replies
7
Views
492
offthelip
O

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,021
Latest member
pingpong7117

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