Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Add Sheets 2, 3, 4 etc. to template that manipulates the data on Sheet 1

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
0
Mark Harris
Asked:
Mark Harris
  • 6
  • 5
1 Solution
 
patrickabCommented:
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
0
 
Mark HarrisAuthor Commented:
No it is fictitious but accurate in format.  Does this mean you might be able to work on this?
0
 
patrickabCommented:
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


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

Open in new window

PayrollExport-2-01.xls
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Mark HarrisAuthor Commented:
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:
<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

Open in new window


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

Open in new window


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

Open in new window


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

Open in new window


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

Open in new window


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

Open in new window


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

Open in new window


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
0
 
Mark HarrisAuthor Commented:
CHANGE****
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

Open in new window

0
 
patrickabCommented:
fireman7147,

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

Open in new window

PayrollExport-2-03.xls
0
 
Mark HarrisAuthor Commented:
Great.
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

Open in new window


I added in this
.Columns("C:C").NumberFormat = "0.00"

Open in new window


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

Open in new window



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

Open in new window


Thanks
0
 
patrickabCommented:
fireman7147,

Please try the attached file.

Patrick
PayrollExport-2-04.xls
0
 
patrickabCommented:
fireman7147,

Please try the attached updated file.

Patrick
PayrollExport-2-05.xls
0
 
Mark HarrisAuthor Commented:
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:
        .Cells(i + 1, "G").FormulaR1C1 = "=SUMIF(R2C1:R22C1,RC[-6],R2C6:R22C6)"

Open in new window


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.
0
 
Mark HarrisAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now