Mark Harris
asked on
Calculate data and move to new sheets
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
PayrollExport-2-01a.xls
Pleas find attached the rough answers. Getting late here.
Did not put the headers.
Assumed RO Code never changed and that payroll numbers and codes are fixed length.
Formulas need to be filled down a 1000 rows to allow for 600 rows as per your question.
Sorry t osay this again ,but this could have been solved using 2 to 3 SQL statements.
Really think this should be broken down into several questions.
All the formulas are there. its almost what you wanted.
A>
PayrollExport-2-01a--1-.xls
Did not put the headers.
Assumed RO Code never changed and that payroll numbers and codes are fixed length.
Formulas need to be filled down a 1000 rows to allow for 600 rows as per your question.
Sorry t osay this again ,but this could have been solved using 2 to 3 SQL statements.
Really think this should be broken down into several questions.
All the formulas are there. its almost what you wanted.
A>
PayrollExport-2-01a--1-.xls
ASKER
I did as you suggested and i reworked the SQL and I got an easier dataset to work with. If I could give you some points for the SQL suggestion I would, i think this is a solved solution without what you provided. Tell me if I can allot some points somehow.
Thanks Mark
Thanks Mark
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I am thinking about this and it can be solved in Excel. The formulas would be rather complicated but something came up. All these calculations would go much faster using sum and "group by" function in SQL.
Is this a possibility for you? DO you have access to the database ?
It would just faster for me & you!
The combined sort table may take quite a few formulas in excel (and a lot of effort and error trapping) but in SQL it is just distinct clause folllowed by 3 fields. (a few seconds of writing the query...)
Just a few thoughts,
A>