Solved

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

Posted on 2011-02-24
11
256 Views
Last Modified: 2012-05-11
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
Comment
Question by:Mark Harris
  • 6
  • 5
11 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 34971988
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
 

Author Comment

by:Mark Harris
ID: 34972553
No it is fictitious but accurate in format.  Does this mean you might be able to work on this?
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34978292
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
 

Author Comment

by:Mark Harris
ID: 34988793
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
 

Author Comment

by:Mark Harris
ID: 34988825
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

Expert Comment

by:patrickab
ID: 34989118
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
 

Author Comment

by:Mark Harris
ID: 34989488
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
 
LVL 45

Expert Comment

by:patrickab
ID: 34991127
fireman7147,

Please try the attached file.

Patrick
PayrollExport-2-04.xls
0
 
LVL 45

Accepted Solution

by:
patrickab earned 500 total points
ID: 34991146
fireman7147,

Please try the attached updated file.

Patrick
PayrollExport-2-05.xls
0
 

Author Comment

by:Mark Harris
ID: 34992882
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
 

Author Closing Comment

by:Mark Harris
ID: 35026090
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now