Export of Sharepoint List (with lookup columns) to Excel - need to tidy/amend data

(Office 365 Excel) Hello, looking for some help/advice / suggestions for my situation: currently working on a large IT project, what I am needing to achieve is to capture training requirements for end-users and the information captured will be used for scheduling. I am using 3 Sharepoint Lists and then exporting into Excel to analyse.

Bullet point summary of what I am working with

• List A - main list for inputting and collating the information needed.
• List B - a list of business job roles by area, and the number of people in that role (used as a Lookup Column by List A). For context around 9 areas with a total of approx 130 job roles
• List C - list of processes by project stream (used as a Lookup Column by List A). For context 7 project streams each with 70 - 100 processes
• List A will capture a Module Title, and then what scenarios sit under that Title (picklist using Lookup from List C), what roles need to attend training (1 x Lookup column per area (~ 9), used as picklist from List B). The Roles lookup has an additional column that brings in the number of people in the roles selected.

Have mocked up a very simplified version below if that's helpful as a visual (only with 3 rows of data). (sorry for poor quality was a nightmare getting it into 1 pic :/)

5XY26uK.png

(Also, Lookup columns, when they go into excel, do an annoying format of having a ;#XXX#; between the items but I have a macro that clears that, so all they will end up having is a semicolon between as the separator.)

Is there a way I can cleanly and efficiently extract the data from the Lookups into a way that I can use excel to pivot/filter/see clearly/analyse etc?

I am now needing to analyse the results and provide a summary: by role, by area, by project stream etc. Here is where I am having some issues and know there must be a better way. Using Roles as an example, the data is exported to Excel and all the roles selected for that Topic are all bundled in the 1 cell for each area making it impossible to cleanly pivot/filter etc. As are the number of people, making what should be a simple sum of the total per row much harder.

Every way I have tried / tested seems laborious and open to error (e.g. copy/paste whole row, then cut/paste the roles out 1 x per row - so when pivoted the duplicates rows/titles will still only show as 1) - but with the volume this seems crazy.) (also have through of applying filter (role) in Sharepoint, saving view and then exporting but again that's not going to work)

Any suggestions would be much welcomed!
Thanks