Help with reporting on a table with sub column headers

Question

I have a sheet that is updated daily and I need to build some reports off of it.  I feel that a pivot table would work great, but the problem is that there are subcolumns in the data. 

Sorry, unable to paste a file image of what I have from my work network, but its basically it is laid out like this:

• 1 row for each day (continues on for years)
• 5 columns, 1 for each department
• 3 sub-columns under each department; for priority:  low, med, high

In summary, each day has 15 boxes that may have a number in them, its the quantity of tickets of the given priority and department occurred for that day.

Because of the sub-columns I can't properly just select the long table and pivot on the data.  I tried mapping it to a new tab sheet with a simpler structure that had 4 columms; Date, Department, Priority, Tickets.  But I can't figure out how to populate it with drag and drop and it would take many many hours of typing to do it manually; tried with index as well, but still couldn't populate the new table to pivot on. 

Remember, the original table is being updated daily so I can't really change that...

I need some help, I'm beyond my excel skill and creativity.

Thanks in advance,

Andy

Answer 1

Andy,

With Get & Transform aka Power Query. Sample avail. here (dowload a copy):

#1 Update the parameters in the Params sheet

#2 Refresh the pivot in Sheet1

Assumptions:

• Data start in row 1, column A in the XL source file
• No other columns are used in the source sheet

Notes:

• Will work if more than 5 Dept
• Rows/Columns with no data/value are not reported, adjustable if required

Answer 2

Have a look here:

https://www.excelguru.ca/blog/2014/09/24/un-pivoting-with-subcategories-in-power-query/

Andreas.

Answer 3

Hi,

See if my solution here helps - Rearrange a multi heading dataset into a single heading one which is Pivot ready.

Answer 4

Andy/Eubanksae

I updated my previous proposal with 2 little optimizations - same link