Data re-organisation

Copper Contributor

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


2 Replies


If all users have Office 2021 or Microsoft 365 it's quite simple:

Thanks Ill will look it over. I knew there was a simpler solution than what i was doing. :)