Data re-organisation

Ref "test.xlsx"

I receive a file with a scene column (in Orange) and a role Column (in blue).

The role Column has a number that identifies an actor. So we see each scene and what actor is in that specific scene. 

On the lower array the data is inverted, we can see the role number (in the green column) and all the scene numbers for that actor (in yellow) separated by a comma. 

What i would like to do, is to automate the yellow section of the grid. i dont know if a formula or a macro can do this. If you know of a way to do this and can point me in the right direction that would be appreciated. Ive tried different things but cant seem to get the logic right. 

Its a file that will be passed around on Mac and PC. if that changes anything

Thanks  A lot