Solved

Calculate data and move to new sheets

Posted on 2011-03-01
4
192 Views
Last Modified: 2012-05-11

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
0
Comment
Question by:Mark Harris
  • 3
4 Comments
 
LVL 6

Expert Comment

by:akajohn
ID: 35009415
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>
0
 
LVL 6

Expert Comment

by:akajohn
ID: 35013510
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
0
 

Author Comment

by:Mark Harris
ID: 35023589
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
0
 
LVL 6

Accepted Solution

by:
akajohn earned 500 total points
ID: 35025569
Hi,

Am glad you did the SQL way because it is faster. I thought you had no control over the output of the db. I am fairly familiar with DBs and doing a SQL

Select ..... sum(hours)   from table        group by pay code, ro type would be be your first table. As simple as that.
Similarly using if else statements you could generate the second table.

As for the points, I leave the decision to you:
You can assign A or B  grades to the answer.

If you take the excel solution, I totally admit It is a half-baked solution and you grade it lower but if you think the SQL suggestion saved you time and optimised the solution to you problem, please grade it accordingly. I would leave your question and not delete it as it is a good example of where to use Excel and where to Use SQL (if you have access to it).
Glad to help,

A>
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

708 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

14 Experts available now in Live!

Get 1:1 Help Now