+ Reply to Thread
Results 1 to 6 of 6

Consolidated Report

  1. #1
    Registered User
    Join Date
    04-28-2017
    Location
    Albuquerque, NM
    MS-Off Ver
    2016
    Posts
    3

    Consolidated Report

    Hi everyone,

    So I have to run a report on all employees at my university every few weeks. In the automatically generated report I receive, the header column is the department name, the header row is the job type. The report breaks each department down so that each row represents the total number of a specific type of employee for that specific department, leaving 0's for the remaining cells in that row corresponding to the rest of the job types. This means that each department is listed multiple times, one for each type of employee working in that department. What I need is to consolidate the report so that each row tallies up all of the job types and puts them in one row for each department, and then groups departments in their respective building numbers. I attached a simplified version of this, the first sheet showing what I have to work with and the second sheet showing what I'm trying to achieve. The real report is typically 13,000-15,000 rows long and it changes week to week as people move in and out of positions across campus. I tried playing around with the Pivot Table options, but couldn't find a way to get it to display the way I wanted. I'm guessing there's got to be a simple way to do this but I can't figure it out. I would greatly appreciate any help with this! Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,952

    Re: Consolidated Report

    In C2 of "Consolidated Report"

    =SUMIFS(INDEX('Original Report'!$C:$I,,MATCH(C$1,'Original Report'!$C$1:$I$1,0)),'Original Report'!$A:$A,$A2,'Original Report'!$B:$B,$B2)

    Copy across to I and down to row 4
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-28-2017
    Location
    Albuquerque, NM
    MS-Off Ver
    2016
    Posts
    3

    Re: Consolidated Report

    That's it! Thank you so much!

    Now how would I collapse the first two columns down to just one department per line? (while still showing each department's building number)

    Thanks again!

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,952

    Re: Consolidated Report

    Not sure I follow: can you post sample of output required?

  5. #5
    Registered User
    Join Date
    04-28-2017
    Location
    Albuquerque, NM
    MS-Off Ver
    2016
    Posts
    3

    Re: Consolidated Report

    On the original report it lists each department (column b) multiple times and there may be multiple departments for each building. I need to consolidate the list so that each department is listed only once, with their corresponding building number as well. Here's another example of what I mean (attached). The first set of columns is how the original report looks. The second set of columns is how I want it to look in the end. On the first attachment I uploaded, I did this with just the three departments listed. I expanded this one to ten departments in five different buildings. (Also, I had to switch to a mac and I exported the file to an excel file so if it looks weird, that's why)
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,952

    Re: Consolidated Report

    ... which is what was provided: SUM by building and department.

    =SUMIFS(INDEX('Original Report'!$C:$I,,MATCH(C$1,'Original Report'!$C$1:$I$1,0)),'Original Report'!$A:$A,$A2,'Original Report'!$B:$B,$B2)

    A2=Building Number

    B2=Department

    Columns a and B of Consolidated Report just need buildings/departments listed as per your last post.

+ 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. Replies: 4
    Last Post: 08-18-2015, 10:02 AM
  2. Replies: 6
    Last Post: 05-01-2015, 12:02 PM
  3. [SOLVED] PowerPivot reports with consolidated report from two tables.
    By nsr1989 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 12-18-2013, 06:42 PM
  4. Consolidated Monthly Report
    By DLC in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-26-2013, 03:40 PM
  5. [SOLVED] Date in Consolidated sheet does not match date in sheets being consolidated
    By Lowtech in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-15-2012, 07:23 PM
  6. Consolidated Report building using mega formula or VBA
    By excel5111987 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-17-2011, 09:18 AM
  7. Run-Time Error '1004' Cannot open PivotTable source file 'Consolidated Report'
    By Aswathy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-30-2009, 01:06 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