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.

[hanykowska]

Hi @markprior!

I think I got what you wanted. The trick is to transpose the columns, then do some cleaning up and filtering, and crosstab them into a new setting. Here's the workflow

The CPL and Sitting columns have empty values instead of NULL but that shouldn't be a problem to fix.

Let me know if that helps or if you have any further questions! 🙂

[hanykowska · Accepted solution]

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!

[markprior]

Thank you @hanykowska for looking into this, I'll check it out and let you know how I get on.