Stacking Static and Dynamic data

markprior

I've got a data set  of people who are sitting exams. Details for the exams are listed in columns horizontally across the spreadsheet and can be identified by the prefix "CPL - " then the exam ID, and next to this column there is an exam sitting date which can be identified by the prefix "Sitting - " then then exam ID.

To complicate matter there is also a Resits column which I would like to stack into the dataset, but not for every exam and this has, these column are identified by having the word "Resits" in the column heading. There is also the possibility that nex exams will be added into the input sheet, hence the dynamic element of this.

I have included an example Excel file with two tabs, one is the Input data, I have highlighted in green the static headers and in blue a sample of the header I have included as part of the second tab which is the desired output.

Any help on steps to complete what I'm after would be much appreciated as so far I've not got very far at all.

Sample File.xlsx

hanykowska

Apologies, @markprior , I forgot about resits in my previous answer!

Here's updated workflow

I had to update the filer tool and add a formula as the columns with Resits have a different format.

Decided to put Resits as a separate column, here's the output I got

Hope this helps!

stack data.yxzp