Using a PivotTable to create a simplified table

[Moosles]

I'm trying to covert a table of data into a more usable format - currently the data is laid out like this, with the hours for each resource broken down by task as shown below:

Task Name	Resource 1	Resource 2	Resource 3	Resource 4	Resource 5	Resource 6
Task #1		6	10			3
Task #2	8			3
Task #3			2.5			8
Task #4			4		1
Task #5		4		2

Obviously this is a horrible layout of data to work with, so I want to simplify it to something more along the lines of the following:

Task Name	Resource	Hours
Task #1	Resource 2	6
Task #1	Resource 3	10
Task #1	Resource 6	3
Task #2	Resource 1	8
Task #2	Resource 4	3
Task #3	Resource 3	2.5
Task #3	Resource 2	8
Task #4	Resource 3	4
Task #4	Resource 5	1
Task #5	Resource 2	4
Task #5	Resource 4	2

I could do this easily enough using an array formula, but the source data table is massive so this would slow the rest of my workbook down drastically which I want to avoid if possible - also it would mean that I would have to include every possible combination of Task Name and Resource Number in the table, so the output would be huge too (with most of the rows being zero). Instead I thought using a PivotTable to arrange the data in the desired format would be doable, but I can't seem to work out a method to do this. How would I go about doing this?

Thanks in advance!

 

[RoryA]

Use Power Query - it's a simple unpivot step.

 

[Moosles]

Hi Rory,

Thanks for this, it took a bit of experimenting to work out how to use Power Query, but this worked perfectly. Thanks!