Pivot columns and Extract without PowerQuery

I have a problem which I can solve using PowerQuery but I need a formula to do it as PowerQuery doesn't allow me to update the data set with new headers and columns.

Raw data

1.jpg

From my raw data, I've highlighted the columns I need. Note that the patterns for the columns repeat. In the raw data I've listed 2 example Dept (Dept1A and Dept2A). From column D, you can assume the pattern continues like Dept1ALevel1, Hour - Dept1ALevel1, Comment - Dept1ALevel1 and so forth up to Dept20ALevel20.

I've also attached my desired result in my excel.

If someone knows how do i translate my power query commands regardless if the headers change that would be very helpful.

Thank you

@AliGW, your solution did not work. That is an exact copy of my dataset (minus the confidential info).

When you run it from your end, consider changing the Header in column D "Dept1ALevel1" to "Dept1ALevel11", it fails because the data set that I always get is different in header but the pattern remain the same i.e the first 3 columns are "Name", "Hours" and "Comment", but then the next columns come in groups of 3s.

First one being Dept or Org or Div what ever, Followed by Hours - blah blah and then Comment, this repeats over and over in groups of 3s.

I only want the first two pivoted automatically without having to do it via power query every time.

I did have a look at the other solution, it runs into the same problem. I can do it using power query but it fails once the dataset changes with different headers. Power query hard codes the headers which i don't want... i want to reference the columns without hard coding the header if that makes sense. Thank you for looking into this problem.

There will be 16 set of 3's.