Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Calculate data and move to new sheets

Posted on 2011-03-01
Medium Priority
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.

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

Question by:Mark Harris
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3

Expert Comment

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,


Expert Comment

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.



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

Accepted Solution

akajohn earned 2000 total points
ID: 35025569

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,


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

715 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