Link to home
Start Free TrialLog in
Avatar of Mark Harris
Mark HarrisFlag for United States of America

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

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

PayrollExport-2-01a.xls
Avatar of Anil
Anil
Flag of United Kingdom of Great Britain and Northern Ireland image

hI fireman7147,

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>
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
Avatar of Mark Harris

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
ASKER CERTIFIED SOLUTION
Avatar of Anil
Anil
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial