Converting MS Forms multi-select columns into a skills × band matrix

Hi all,

I’m working on a workforce mapping project aligning skills to levels of practice and need some help restructuring an Excel dataset exported from Microsoft Forms.

In the form, respondents ticked multiple clinical skills per staff band (e.g. staff at different pay grades/levels tick skills they perform). In the Excel export, each pay grade's responses appear as one column containing a comma/semicolon-separated list of selected skills. I need a skills x band matrix (yes or no) per service. I also have a similar issue with training/education and pay grade/levels.

I have 70 services which I have collected data on, pay grades range from 2-9. Is there an easy way for me to do this especially avoiding duplication and keeping it auditable? (this is for a professional report, not a dashboard). Data is anonymised and I’m happy to share if helpful.

Thanks in advance