How to fix my data

Apologies if I don’t explain this very well!
This issue is around my data, and how I can fix this so it works with a pivot table correctly. But if there is a better way to do this, other than a pivot table, please let me know. I want one line for each member of staff listed in column I and then repeat the exercise so I have one line for each of the weeks this occurs, so column E has “2, 4-8” This means the activity takes place in week 2, and in weeks 4,5,6,7 and 8.

Data: attached is an example of the format it comes in.
What I’d like to be able to:
Filter by Module ID (Column A)
Filter for week number (Column E) that will then give me a list of staff who have activities that week
(staff listed on rows, and activities type (Column D) as column headers. Image of what i'd like attached - I'm i'm open to suggestions

FYI the data will change every year!


Any suggestions?

The way your data is entered makes things painful

for example I assume e5 needs splitting from 2, 4-8 into 2, 4,5,6,7,8
And likewise you need to split the delimited staff in sperate values for each staff member

How big is the actual file you need to look at?
Do you know all the names of the staff to report on (does it exist in a sperate list)? If yes it can be matched against to get the answer.
i suspect a powerquery or VBA solution will be the only way as the data you have given us needs to be transformed before a pivot can be used