+ Reply to Thread
Results 1 to 11 of 11

Formula to consolidate hours (multiple columns of criteria)

  1. #1
    Registered User
    Join Date
    05-08-2015
    Location
    Oxnard, CA
    MS-Off Ver
    2007
    Posts
    9

    Formula to consolidate hours (multiple columns of criteria)

    Hi,
    I have another question/problem and am coming to you for help...

    Attached is the spreadsheet in question.

    - Column A: Employee
    - Column B: Hours
    - Column C: Work Description

    I'd like to see which work description had the most hours, by employee. The subtotal feature did not accomplish this for me I've got over 1100 employees to do this for. Some employees worked one job, some worked over 10.

    Aaron 10 Log Vault 312 Manifold
    Aaron 10 Log Vault 312 Manifold
    Aaron 10 Log Vault 312 Manifold
    Aaron 10 Log Vault 312 Manifold
    Aaron 10 Log Vault 312 Manifold
    Aaron 10 Log Vault 312 Manifold
    Aaron 10 Log Vault 312 Manifold
    Aaron 5 Log Vault 312 Manifold
    Aaron 1.5 Boiler Outage
    Aaron 1.5 Boiler Outage
    Aaron 1.5 Boiler Outage
    Aaron 10 Boiler Outage
    Aaron 10 Boiler Outage
    Aaron 10 Boiler Outage
    Aaron 8 Boiler Outage
    Aaron 9 H2S Monitoring
    Aaron 6 H2S Monitoring
    Aaron 1 H2S Monitoring
    Aaron 1.5 H2S Monitoring
    Aaron 8 H2S Monitoring
    Aaron 3.5 H2S Monitoring
    Aaron 6.5 H2S Monitoring
    Aaron 8 H2S Monitoring
    Hak 8 6" Drain Line
    Hak 3 6" Drain Line
    Hak 8 6" Drain Line
    Hak 8 6" Drain Line
    Hak 8 Fab 10 ea chem clean hdrs
    Hak 8 Fab 10 ea chem clean hdrs
    Hak 8 Fab 10 ea chem clean hdrs
    Hak 8 Fab 10 ea chem clean hdrs
    Hak 7 Fab 10 ea chem clean hdrs
    Hak 4 Fab 10 ea chem clean hdrs
    Hak 2 Fab 10 ea chem clean hdrs
    Hak 8 Fab HA/HE Quadrant frames
    Hak 8 Fab HA/HE Quadrant frames
    Hak 8 Fab HA/HE Quadrant frames
    Hak 8 Fab HA/HE Quadrant frames
    Hak 8 Fab HA/HE Quadrant frames
    Hak 8 Fab HA/HE Quadrant frames
    Hak 2 Fab HA/HE Quadrant frames
    Hak 2 Fab HA/HE Quadrant frames
    Hak 4 Fab HA/HE Quadrant frames
    Hak 3.5 Fab HA/HE Quadrant frames
    Hak 2 Fab HA/HE Quadrant frames
    Hak 2 Fab HA/HE Quadrant frames
    Hak 2.5 Fab HA/HE Quadrant frames
    Hak 8 Fab unit 65
    Hak 8 Fab unit 65

    Thank you again

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Formula to consolidate hours (multiple columns of criteria)

    probably a pivot table

    see attached
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    05-08-2015
    Location
    Oxnard, CA
    MS-Off Ver
    2007
    Posts
    9

    Re: Formula to consolidate hours (multiple columns of criteria)

    Ah that could work... Let me try it out and I'll be back. Thank you!
    Last edited by egordon; 05-08-2015 at 04:31 PM.

  4. #4
    Registered User
    Join Date
    05-08-2015
    Location
    Oxnard, CA
    MS-Off Ver
    2007
    Posts
    9

    Re: Formula to consolidate hours (multiple columns of criteria)

    The pivot table was did not work

    If an employee had 10 jobs, the employees name only shows up one time. It's close...
    Last edited by egordon; 05-08-2015 at 04:57 PM.

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Formula to consolidate hours (multiple columns of criteria)

    what version of Excel

    I went to design
    Report layout
    > Tabular Form
    subtotals > none
    Sort by JobTime, descending

  6. #6
    Registered User
    Join Date
    05-08-2015
    Location
    Oxnard, CA
    MS-Off Ver
    2007
    Posts
    9

    Re: Formula to consolidate hours (multiple columns of criteria)

    OK got it! I took your advice, and then copied the data into another tab, and removed the jobs that were blank (after they were sorted) and removed those... it left me with the highest number of hours per job per person. Yay! Thank you!!

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Formula to consolidate hours (multiple columns of criteria)

    you can leave the blanks in - and use the filter on the pivot table to remove blanks - if you wanted to

  8. #8
    Registered User
    Join Date
    05-08-2015
    Location
    Oxnard, CA
    MS-Off Ver
    2007
    Posts
    9

    Re: Formula to consolidate hours (multiple columns of criteria)

    Thank you again, ETAF... worked like a charm. And it taught me how to use a pivot table better.

  9. #9
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Formula to consolidate hours (multiple columns of criteria)

    your welcome - thanks for the rep

  10. #10
    Registered User
    Join Date
    05-08-2015
    Location
    Oxnard, CA
    MS-Off Ver
    2007
    Posts
    9

    Re: Formula to consolidate hours (multiple columns of criteria)

    Hi,
    I'm back again...

    Tried this on the same style of report, and sorted by the number of hours to obtain the higher hours per person per job, and this happened:

    Stephen Joseph Missouri Elect Services 26
    Arkansas Elect Services 62

    No matter what sort feature I am using in the pivot table, it is sorting it by name, and by description of work and then by hours...

  11. #11
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Formula to consolidate hours (multiple columns of criteria)

    is this already a pivot table - as you dont have a name
    but
    a sample spreadsheet would help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Consolidate Data In Multiple Rows and Columns Into One Row
    By KiwDaWabbit in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-09-2015, 03:06 PM
  2. Consolidate multiple columns and rows into one Column
    By jewels3059 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-09-2013, 11:12 AM
  3. [SOLVED] Need formula to count multiple criteria in 2 columns
    By tancho321 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-22-2013, 04:53 PM
  4. [SOLVED] Formula for Criteria in Multiple Columns
    By Charlie C in forum Excel General
    Replies: 16
    Last Post: 03-29-2012, 04:39 PM
  5. Consolidate rows from multiple worksheets in one based on cell criteria
    By SuperSman in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-11-2011, 10:28 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1