We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
COURSE of the MONTH
14 days, 12 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Add Sheets 2, 3, 4 etc. to template that manipulates the data on Sheet 1
Posted on 2011-02-24
Hello Experts,
I have an excel template that extracts data from an SQL db and formats it. The number of rows will vary based on the date ranges selected.
I want to add a few sheets that can manipulate the extracted data. (There is no need to make additional queries to DB)
I have included the original template with out any data in it. I also have included a workbook that has sample data on Sheet 1, and all the additional sheets that need to be generated as well.
Please let me know if you need more information and Thank you!
Mark Harris
PayrollExport.xls
PayrollExport.xlt
I have an excel template that extracts data from an SQL db and formats it. The number of rows will vary based on the date ranges selected.
I want to add a few sheets that can manipulate the extracted data. (There is no need to make additional queries to DB)
I have included the original template with out any data in it. I also have included a workbook that has sample data on Sheet 1, and all the additional sheets that need to be generated as well.
Please let me know if you need more information and Thank you!
Mark Harris
PayrollExport.xls
PayrollExport.xlt
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
6
5
11 Comments
fireman7147,
I do hope that none of the information in those files is personal as it is now publicly available - since you uploaded the files.
Patrick
I do hope that none of the information in those files is personal as it is now publicly available - since you uploaded the files.
Patrick
fireman7147,
>Does this mean you might be able to work on this?
Either way I can work on it. I was only concerned that the data might be private, however I'm pleased to hear it's all ficticious.
Try the macro - code below - in the attached file. Press the button on the CombinedSort sheet.
Hope it helps
Patrick
>Does this mean you might be able to work on this?
Either way I can work on it. I was only concerned that the data might be private, however I'm pleased to hear it's all ficticious.
Try the macro - code below - in the attached file. Press the button on the CombinedSort sheet.
Hope it helps
Patrick
Sub specialmacro()
Dim rng As Range
Dim celle As Range
Dim coll As New Collection
Dim i As Long
Dim str1 As String
With Sheets("Payroll")
Set rng = Range(.Cells(5, "C"), .Cells(.Rows.Count, "C").End(xlUp))
End With
For Each celle In rng
str1 = Left(celle, Len(celle) - 1) & CStr(celle.Offset(0, 1)) & celle.Offset(0, 4)
On Error Resume Next
coll.Add str1, str1
Next celle
With Sheets("CombinedSort")
For i = 1 To coll.Count
.Cells(i + 21, "A") = Left(coll(i), Len(coll(i)) - 10)
.Cells(i + 21, "B") = --Mid(coll(i), Len(coll(i)) - 9, 8)
.Cells(i + 21, "C") = "=SUMPRODUCT((Payroll!R[-17]C[1]:R[9]C[1]=CombinedSort!RC[-1])*(Payroll!R[-17]C[4]:R[9]C[4]=CombinedSort!RC[1])*Payroll!R[-17]C[2]:R[9]C[2])"
.Cells(i + 21, "D") = Right(coll(i), 2)
.Cells(i + 21, "E").FormulaR1C1 = "=VLOOKUP(RC[-1],rates,2,0)"
.Cells(i + 21, "F").FormulaR1C1 = "=RC[-3]*RC[-1]"
Next i
.Columns("F:F").NumberFormat = "0.00"
End With
End Sub
Ok, What you did works but I probably didn't explain clearly what I need.
I have the template excel sheet that has 2 Sheets in it. Payroll and FLSA. The Payroll Sheet is being populated via an SQL query from a DB. I need to add 3 Sheets to the TEMPLATE that will do calculations based on the PAYROLL Sheet.
**IMPORTANT**
1. The number of rows will vary from payroll to payroll. It could be 60 rows or 600+ rows there is no way to tell in advance, however the SQL numbers each row so I suppose the last row number(#) could be used somehow as a total number of records.
2. Employees might have a code only on a single day in the entire range, while other employees might have a code on several or even all days in the range. It is also possible for an employee to have more than one code on a single day in the range.
3. The Additional Template Sheets can be named Rates, CombinedSort, and ROFinal and can be the last 3 on the WB in that order is fine.
4. We have employees with exact same names so payroll ID is unique.
5. The CombinedSort and ROFinal Sheets do not need the big header that is on the Payroll Sheer, only the column headers you see below.
So... Payroll looks like this:
Jan Kerr had the following Rows:
CombinedSort Sheet will add like RO Types. Since Jan has 2 EN codes we need to SUM the hours:
CombinedSort will also VLOOKUP the RateNum from the Rates Sheet and multiply that number by the Hours for each RO Type:
The entire CombinedSort will be a subtotal of each ROType for SORTED by each Employee will look like this:
The Rate Sheet is an editable list like this:
The final Sheet needed in the template will be a sheet that totals all the Pay by Employee.
I will reattach a template that has blank CombinedSort and ROFinal Sheets along with Example Sheets of each at the end. PayrollExport-2-01a.xls
I have the template excel sheet that has 2 Sheets in it. Payroll and FLSA. The Payroll Sheet is being populated via an SQL query from a DB. I need to add 3 Sheets to the TEMPLATE that will do calculations based on the PAYROLL Sheet.
**IMPORTANT**
1. The number of rows will vary from payroll to payroll. It could be 60 rows or 600+ rows there is no way to tell in advance, however the SQL numbers each row so I suppose the last row number(#) could be used somehow as a total number of records.
2. Employees might have a code only on a single day in the entire range, while other employees might have a code on several or even all days in the range. It is also possible for an employee to have more than one code on a single day in the range.
3. The Additional Template Sheets can be named Rates, CombinedSort, and ROFinal and can be the last 3 on the WB in that order is fine.
4. We have employees with exact same names so payroll ID is unique.
5. The CombinedSort and ROFinal Sheets do not need the big header that is on the Payroll Sheer, only the column headers you see below.
So... Payroll looks like this:
<IFDAgency Name>
Payroll Report
02/01/2011 through 02/03/2011
# Date Name Payroll ID Hours PCde RO Type Unit
1 2/1/2011 Booker, Mike 65327915 24 RO AO LD04
2 2/1/2011 Smith, Dave M 24513579 15 RO SC EG01
3 2/1/2011 Polk, Jane 10749635 12 RO BC C1205
4 2/1/2011 Rivers, Dan 10987340 8 RO LT EG10
5 2/2/2011 Conners, John 59832140 6.25 RO CT LD01
6 2/2/2011 Polk, Jane 10749635 24 RO CT SQ10
7 2/2/2011 Meyers, Phil 30010241 24 RO EN BC11
8 2/2/2011 Anderson, Bob 74659315 12 RO SC EG31
9 2/2/2011 Davis, Sam 56471026 8 RO CT LD33
10 2/2/2011 Rivers, Dan 10987340 4.25 RO EN EG19
11 2/2/2011 Smith, Dave M 24513579 3 RO EN SQ07
12 2/2/2011 Karr, Jan 47100321 15 RO EN EG023
13 2/3/2011 Rivers, Dan 10987340 12 RO LT C1203
14 2/3/2011 Conners, John 59832140 24 RO LT BC14
15 2/3/2011 Davis, Sam 56471026 19 RO CT EG07
16 2/3/2011 Karr, Jan 47100321 8 RO AO EG16
17 2/3/2011 Johnson, Pete 97415307 8 RO EN EG12
18 2/3/2011 Meyers, Phil 30010241 24 RO LT LD07
19 2/4/2011 Booker, Mike 65327915 24 RO EN LD31
20 2/4/2011 Polk, Jane 10749635 24 RO CT C1401
21 2/4/2011 Rivers, Dan 10987340 14 RO EN C1201
22 2/4/2011 Karr, Jan 47100321 6 RO EN LD45
23 2/5/2011 Williams, Sara 54320174 8 RO CT EG01
24 2/5/2011 Karr, Jan 47100321 4 RO CT EG10
25 2/5/2011 Anderson, Bob 74659315 7.5 RO AO LD14
26 2/5/2011 Polk, Jane 10749635 6 RO BC LD01
27 2/5/2011 Rivers, Dan 10987340 4 RO EN EG19
28 2/5/2011 Rivers, Dan 10987340 8 RO LT EG19
29 2/5/2011 Smith, Dave R 98765439 12 RO EN EG100
Jan Kerr had the following Rows:
2/2/2011 Karr, Jan 47100321 15 RO EN EG023
2/3/2011 Karr, Jan 47100321 8 RO AO EG16
2/4/2011 Karr, Jan 47100321 6 RO EN LD45
2/5/2011 Karr, Jan 47100321 4 RO CT EG10
CombinedSort Sheet will add like RO Types. Since Jan has 2 EN codes we need to SUM the hours:
Karr, Jan 47100321 8.00 RO AO
Karr, Jan 47100321 21.00 RO EN
Karr, Jan 47100321 4.00 RO CT
CombinedSort will also VLOOKUP the RateNum from the Rates Sheet and multiply that number by the Hours for each RO Type:
Karr, Jan 47100321 8.00 RO AO 17.95 143.60
Karr, Jan 47100321 21.00 RO EN 10.25 215.25
Karr, Jan 47100321 4.00 RO CT 13.75 55.00
The entire CombinedSort will be a subtotal of each ROType for SORTED by each Employee will look like this:
Name Payroll ID Hours Pay Code RO Type
Booker, Mike 65327915 24.00 RO AO
Booker, Mike 65327915 24.00 RO EN
Polk, Jane 10749635 18.00 RO BC
Polk, Jane 10749635 48.00 RO CT
Rivers, Dan 10987340 22.25 RO EN
Rivers, Dan 10987340 28.00 RO LT
Smith, Dave M 24513579 3.00 RO EN
Smith, Dave M 24513579 15.00 RO SC
Meyers, Phil 30010241 24.00 RO EN
Meyers, Phil 30010241 24.00 RO LT
Karr, Jan 47100321 8.00 RO AO
Karr, Jan 47100321 21.00 RO EN
Karr, Jan 47100321 4.00 RO CT
Williams, Sara 54320174 8.00 RO CT
Davis, Sam 56471026 27.00 RO CT
Conners, John 59832140 6.25 RO CT
Conners, John 59832140 24.00 RO LT
Anderson, Bob 74659315 7.50 RO AO
Anderson, Bob 74659315 12.00 RO SC
Johnson, Pete 97415307 8.00 RO EN
Smith, Dave R 98765439 12.00 RO EN
The Rate Sheet is an editable list like this:
RateCode RateNum
SC 21.75
BC 19.25
AO 17.95
CT 13.75
LT 13.75
EN 10.25
The final Sheet needed in the template will be a sheet that totals all the Pay by Employee.
Name Payroll ID Pay Total
Booker, Mike 65327915 676.80
Polk, Jane 10749635 1006.50
Rivers, Dan 10987340 613.06
Smith, Dave M 24513579 357.00
Meyers, Phil 30010241 576.00
Karr, Jan 47100321 413.85
Williams, Sara 54320174 110.00
Davis, Sam 56471026 371.25
Conners, John 59832140 415.94
Anderson, Bob 74659315 395.63
Johnson, Pete 97415307 82.00
Smith, Dave R 98765439 123.00
I will reattach a template that has blank CombinedSort and ROFinal Sheets along with Example Sheets of each at the end. PayrollExport-2-01a.xls
CHANGE****
CombinedSort Should Actually look like this:
CombinedSort Should Actually look like this:
Name Payroll ID HoursPCde RO Type RateNum Pay
Booker, Mike 65327915 24.00 RO AO 17.95 430.80
Booker, Mike 65327915 24.00 RO EN 10.25 246.00
Polk, Jane 10749635 18.00 RO BC 19.25 346.50
Polk, Jane 10749635 48.00 RO CT 13.75 660.00
Rivers, Dan 10987340 22.25 RO EN 10.25 228.06
Rivers, Dan 10987340 28.00 RO LT 13.75 385.00
Smith, Dave M 24513579 3.00 RO EN 10.25 30.75
Smith, Dave M 24513579 15.00 RO SC 21.75 326.25
Meyers, Phil 30010241 24.00 RO EN 10.25 246.00
Meyers, Phil 30010241 24.00 RO LT 13.75 330.00
Karr, Jan 47100321 8.00 RO AO 17.95 143.60
Karr, Jan 47100321 21.00 RO EN 10.25 215.25
Karr, Jan 47100321 4.00 RO CT 13.75 55.00
Williams, Sara 54320174 8.00 RO CT 13.75 110.00
Davis, Sam 56471026 27.00 RO CT 13.75 371.25
Conners, John 59832140 6.25 RO CT 13.75 85.94
Conners, John 59832140 24.00 RO LT 13.75 330.00
Anderson, Bob 74659315 7.50 RO AO 17.95 134.63
Anderson, Bob 74659315 12.00 RO SC 21.75 261.00
Johnson, Pete 97415307 8.00 RO EN 10.25 82.00
Smith, Dave R 98765439 12.00 RO EN 10.25 123.00
fireman7147,
Hopefully the attached file gives you what you are wanting.
Patrick
Hopefully the attached file gives you what you are wanting.
Patrick
Sub specialmacro()
Dim rng As Range
Dim celle As Range
Dim coll As New Collection
Dim i As Long
Dim str1 As String
With Sheets("Payroll")
Set rng = Range(.Cells(5, "C"), .Cells(.Rows.Count, "C").End(xlUp))
End With
For Each celle In rng
str1 = Left(celle, Len(celle) - 1) & CStr(celle.Offset(0, 1)) & celle.Offset(0, 4)
On Error Resume Next
coll.Add str1, str1
Next celle
With Sheets("CombinedSort")
For i = 1 To coll.Count
.Cells(i + 1, "A") = Left(coll(i), Len(coll(i)) - 10)
.Cells(i + 1, "B") = --Mid(coll(i), Len(coll(i)) - 9, 8)
.Cells(i + 1, "C") = "=SUMPRODUCT((Payroll!R[3]C[1]:R[29]C[1]=CombinedSort!RC[-1])*(Payroll!R[3]C[4]:R[29]C[4]=CombinedSort!RC[1])*Payroll!R[3]C[2]:R[29]C[2])"
.Cells(i + 1, "D") = Right(coll(i), 2)
.Cells(i + 1, "E").FormulaR1C1 = "=VLOOKUP(RC[-1],rates,2,0)"
.Cells(i + 1, "F").FormulaR1C1 = "=RC[-3]*RC[-1]"
Next i
.Columns("F:F").NumberFormat = "0.00"
End With
End Sub
Great.
This is what I get:
I added in this
I need to fix up 2 items.
1. I wanted to Sort these by Name ASC (If I can have a button to Sort by PayrollID and one by Name that would be great so I can toggle back and forth)
2. The name field is parsed. Can I have all the name?
NEXT
You didn't add a Macro to the ROFinal Sheet. I need the ROFinal Sheet to have a macro that will create a single row per employee with all the Pay Due them
Thanks
This is what I get:
Employee PayrollID Hours RteCd RateNum Pay
Booker, Mik 65327915 24.00 AO 17.95 430.80
Smith, Dave 24513579 15.00 SC 21.75 326.25
Polk, Jan 10749635 18.00 BC 19.25 346.50
Rivers, Da 10987340 28.00 LT 13.75 385.00
Conners, Joh 59832140 6.25 CT 13.75 85.94
Polk, Jan 10749635 48.00 CT 13.75 660.00
Meyers, Phi 30010241 24.00 EN 10.25 246.00
Anderson, Bo 74659315 12.00 SC 21.75 261.00
Davis, Sa 56471026 27.00 CT 13.75 371.25
Rivers, Da 10987340 22.25 EN 10.25 228.06
Smith, Dave 24513579 3.00 EN 10.25 30.75
Karr, Ja 47100321 21.00 EN 10.25 215.25
Conners, Joh 59832140 24.00 LT 13.75 330.00
Karr, Ja 47100321 8.00 AO 17.95 143.60
Johnson, Pet 97415307 8.00 EN 10.25 82.00
Meyers, Phi 30010241 24.00 LT 13.75 330.00
Booker, Mik 65327915 24.00 EN 10.25 246.00
Williams, Sar 54320174 8.00 CT 13.75 110.00
Karr, Ja 47100321 4.00 CT 13.75 55.00
Anderson, Bo 74659315 7.50 AO 17.95 134.63
Smith, Dave 98765439 12.00 EN 10.25 123.00
I added in this
.Columns("C:C").NumberFormat = "0.00"
I need to fix up 2 items.
1. I wanted to Sort these by Name ASC (If I can have a button to Sort by PayrollID and one by Name that would be great so I can toggle back and forth)
Employee PayrollID Hours RtCde RateNum Pay
Anderson, Bo 74659315 8.00 SC 21.75 174.00
Anderson, Bo 74659315 12.00 AO 17.95 215.40
Booker, Mik 65327915 12.00 AO 17.95 215.40
Booker, Mik 65327915 18.00 EN 10.25 184.50
Conners, Joh 59832140 6.25 CT 13.75 85.94
Conners, Joh 59832140 24.00 LT 13.75 330.00
Davis, Sa 56471026 21.00 CT 13.75 288.75
Johnson, Pet 97415307 24.00 EN 10.25 246.00
Karr, Ja 47100321 0.00 EN 10.25 0.00
Karr, Ja 47100321 6.00 AO 17.95 107.70
Karr, Ja 47100321 0.00 CT 13.75 0.00
Meyers, Phi 30010241 27.00 EN 10.25 276.75
Meyers, Phi 30010241 8.00 LT 13.75 110.00
Polk, Jan 10749635 0.00 BC 19.25 0.00
Polk, Jan 10749635 24.00 CT 13.75 330.00
Rivers, Da 10987340 24.00 LT 13.75 330.00
Rivers, Da 10987340 8.00 EN 10.25 82.00
Smith, Dave 24513579 7.50 SC 21.75 163.13
Smith, Dave 24513579 4.00 EN 10.25 41.00
Smith, Dave 98765439 8.00 EN 10.25 82.00
Williams, Sar 54320174 0.00 CT 13.75 0.00
2. The name field is parsed. Can I have all the name?
NEXT
You didn't add a Macro to the ROFinal Sheet. I need the ROFinal Sheet to have a macro that will create a single row per employee with all the Pay Due them
Name Payroll ID Pay Total
Booker, Mike 65327915 676.80
Polk, Jane 10749635 1006.50
Rivers, Dan 10987340 613.06
Smith, Dave M 24513579 357.00
Meyers, Phil 30010241 576.00
Karr, Jan 47100321 413.85
Williams, Sara 54320174 110.00
Davis, Sam 56471026 371.25
Conners, John 59832140 415.94
Anderson, Bob 74659315 395.63
Johnson, Pete 97415307 82.00
Smith, Dave R 98765439 123.00
Thanks
Thanks, almost ready to award you my friend
Looks pretty much where I need it, However the "G" Column only calculates up to row 22. If I do a Payroll with 100 records, only the first 22 get a result in "G", 23-100 get "0.00".
Remember the Payroll Sheet can be 1 record or 1000+ I think it is this line:
Also, is there no way to have full names appear in CombinedSort and ROFinal?
I am picking up a little on VBA... if you could comment the code a bit, i could probably follow better.
Looks pretty much where I need it, However the "G" Column only calculates up to row 22. If I do a Payroll with 100 records, only the first 22 get a result in "G", 23-100 get "0.00".
Remember the Payroll Sheet can be 1 record or 1000+ I think it is this line:
.Cells(i + 1, "G").FormulaR1C1 = "=SUMIF(R2C1:R22C1,RC[-6],R2C6:R22C6)"
Also, is there no way to have full names appear in CombinedSort and ROFinal?
I am picking up a little on VBA... if you could comment the code a bit, i could probably follow better.
The expert was extremely quick with solutions. For the most part I enjoyed working with this expert. There was an incompletion portion but I only indicated the need after we were well into it so i take a little of the blame for that. All in all the expert provided some very useful code for possible future use if needed.
Featured Post
Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Do you use a spreadsheet like Microsoft's Excel? Have you ever wanted to link out to a non excel file on your computer or network drive? This is the way I found to do it!
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses
Course of the Month14 days, 12 hours left to enroll
771 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.