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

You only chance is to use Power Query and transform the data to a structure that can be used in a Pivot Table.

Power Query aka Get & Transform

If you need further help, we need a sample file.

Andreas.

Answer 2

Thank you, I will check that out now. 

Also, I just created a quick sample of what my data looks like.

Answer 3

I tried to map it to a table that was structured like this; and I can do it manually but too labor intensive.  In this case you would end up with 15 rows for each weekday...

DATE	DEPT	PRIORITY	TICKETS
Mon - 06 Jan 2020	Dept 1	LOW	7
Mon - 06 Jan 2020	Dept 1	MED
Mon - 06 Jan 2020	Dept 1	HIGH
Mon - 06 Jan 2020	Dept 2	LOW	6
Mon - 06 Jan 2020	Dept 2	MED
Mon - 06 Jan 2020	Dept 2	HIGH	1
Mon - 06 Jan 2020	Dept 3	LOW	2

ETC...

Answer 4

Hi

#1 What version of Excel is in use?

#2 On which OS (Windows/Mac)?

Answer 5

Excel 2016 on Windows 10 PC  (the screenshot above was on a mac, but the actual spreadsheet I'm working on is not).